Excel 2010 VBA Referencing Specific Cells in other worksheets

Question

I m sorry this is probably an extremely basic Excel VBA question   I ve just started learning it  and I am not finding very much in the way of good tutorials   I can t find much organised information on the language at all actually   I ve got a couple worksheets called  Sheet1  and  Sheet2   The first and second columns of Sheet1 contain some numbers   I want to write a macro which prints the results of a function taking 2 variables  one each from column A and column B  to Sheet2   but I want to space these results out in the new work sheet so that the result of the function on Column  i  in Sheet1 is put into row 1 and column 4 i   Here s what I ve tried to do so far  but it hasn t worked because I don t know how to reference specific cells in other worksheets properly   Apologies that this is a very newb question  any help is much appreciated   Sub results        Dim i As Integer  noValues As Integer     noValues   Application CountA Range  A A         Sheets  Sheet2   Select     Range  A1   Select     For i   1 To noValues         Range  A1   Offset 0  4    i - 1   Select         ActiveCell FormulaR1C1     Sheet1 A i    Sheet1 B i       Next i  End Sub   Where A i  and B i  should mean the value in Column A or B  row i

User · Accepted Answer

Sub Results2        Dim rCell As Range     Dim shSource As Worksheet     Dim shDest As Worksheet     Dim lCnt As Long      Set shSource   ThisWorkbook Sheets  Sheet1       Set shDest   ThisWorkbook Sheets  Sheet2        For Each rCell In shSource Range  A1   shSource Cells shSource Rows Count  1  End xlUp   Cells         lCnt   lCnt   1         shDest Range  A4   Offset 0  lCnt   4  Formula        amp  rCell Address False  False    True   amp       amp  rCell Offset 0  1  Address False  False    True      Next rCell  End Sub   This loops through column A of sheet1 and creates a formula in sheet2 for every cell   To find the last cell in Sheet1  I start at the bottom  shSource Rows Count  and  End xlUp  to get the last cell in the column that s not blank   To create the elements of the formula  I use the Address property of the cell on Sheet   I m using three of the arguments to Address   The first two are RowAbsolute and ColumnAbsolute  both set to false   I don t care about the third argument  but I set the fourth argument  External  to True so that it includes the sheet name   I prefer to go from Source to Destination rather than the other way   But that s just a personal preference   If you want to work from the destination   Sub Results3        Dim i As Long  lCnt As Long     Dim sh As Worksheet      lCnt   Application WorksheetFunction CountA ThisWorkbook Sheets  Sheet1   Columns 1       Set sh   ThisWorkbook Sheets  Sheet2        Const sSOURCE As String    Sheet1        For i   1 To lCnt         sh Range  A1   Offset 0  4    i - 1   Formula        amp  sSOURCE  amp   A   amp  i  amp         amp  sSOURCE  amp   B   amp  i     Next i  End Sub

User · Answer

I am going to give you a simplistic answer that hopefully will help you with VBA in general   The easiest way to learn how VBA works and how to reference and access elements is to record your macro then edit it in the VBA editor   This is how I learned VBA   It is based on visual basic so all the programming conventions of VB apply  Recording the macro lets you see how to access and do things    you could use something like this   var result   0 Sheets  Sheet1   Select result   Range  A1   Value   Range  B1   Value Sheets  Sheet2   Select Range  D1   Value   result   Alternatively you can also reference a cell using Cells 1 1  Value This way you can set variables and increment them as you wish   I think I am just not clear on exactly what you are trying to do but i hope this helps

User · Answer

Private Sub Click Click     Dim vaFiles As Variant  Dim i As Long  For j   1 To 2 vaFiles   Application GetOpenFilename         FileFilter   Excel Filer    xlsx    xlsx          Title   Open File s    MultiSelect  True   If Not IsArray vaFiles  Then Exit Sub   With Application    ScreenUpdating   False   For i   1 To UBound vaFiles       Workbooks Open vaFiles i       wrkbk name   vaFiles i      Next i    ScreenUpdating   True End With  If j   1 Then work1   Right wrkbk name  Len wrkbk name  - InStrRev wrkbk name        Else  work2   Right wrkbk name  Len wrkbk name  - InStrRev wrkbk name        End If    Next j   Filling the values of the group name   check   Application WorksheetFunction Search Name  work1  check   InStr UCase  Qoute Request    work1   If check   1 Then Application Workbooks work1  Activate Else Application Workbooks work2  Activate End If  ActiveWorkbook Sheets  GI Quote Request   Select ActiveSheet Range  B4 C12   Copy Application Workbooks  Model   Activate ActiveWorkbook Sheets  Request   Range  K3   Select ActiveSheet Paste   Application Workbooks  Model   Activate ActiveWorkbook Sheets  Request   Select  Range  D3   Value   Range  L3   Value Range  D7   Value   Range  L9   Value Range  D11   Value   Range  L7   Value  For i   4 To 5  If i   5 Then GoTo NextIteration End If  If Left ActiveSheet Range  B   amp  i  Value  Len ActiveSheet Range  B   amp  i  Value  - 1    Range  K   amp  i  Value Then     ActiveSheet Range  D   amp  i  Value   Range  L   amp  i  Value  End If  NextIteration  Next i   eligibles part Count   Range  D11   Value For i   27 To Count   24 Range  C   amp  i  EntireRow Offset 1  0  Insert Next i  check   Left work1  InStrRev work1       - 1    check   InStr  Census   work1  If check    Census  Then workbk   work1 Application Workbooks work1  Activate Else Application Workbooks work2  Activate workbk   work2 End If   DOB ActiveWorkbook Sheets  Sheet1   Select ActiveSheet Range  D2   Select ActiveSheet Range Selection  Selection End xlDown   Select Selection Copy  Application Workbooks  Model   Activate ActiveWorkbook Sheets  Request   Select ActiveSheet Range  C27   Select ActiveSheet Paste   Gender Application Workbooks workbk  Activate ActiveWorkbook Sheets  Sheet1   Select ActiveSheet Range  C2   Select ActiveSheet Range Selection  Selection End xlDown   Select Selection Copy  Application Workbooks  Model   Activate ActiveWorkbook Sheets  Request   Select  Application CutCopyMode   False  ActiveSheet Range  k27   Select ActiveSheet Paste  For i   27 To Count   27 ActiveSheet Range  E   amp  i  Value   Left ActiveSheet Range  k   amp  i  Value  1  Next i   Salary Application Workbooks workbk  Activate ActiveWorkbook Sheets  Sheet1   Select ActiveSheet Range  N2   Select ActiveSheet Range Selection  Selection End xlDown   Select Selection Copy  Application Workbooks  Model   Activate ActiveWorkbook Sheets  Request   Select  Application CutCopyMode   False  ActiveSheet Range  F27   Select ActiveSheet Paste   ActiveSheet Range  K3 L   amp  Count  Select selction ClearContents End Sub

[excel] Excel 2010 VBA Referencing Specific Cells in other worksheets

Examples related to excel

Examples related to vba