Need help evaluating cells in another workbook in macro

[kfguardian]

I have found how to directly reference another workbook as such:
ActiveCell.FormulaR1C1 = "=IF(frmInputData.xls!R2C3=""A"",5,0)"

However, I need to do some repetetive work depending on the value in a
column for each row. Can you reference a cell in another workbook using
variables?? My ultimate goal is to move data from certain cells in this
workbook to my active workbook.

 

[Smallweed]

Easiest to use object variables. The following example will go through A1:Z1
on Sheet1 of the second workbook, filling 5 into equivalent cells on Sheet1
of the activeworkbook if the cell has A in it:

Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range
Dim wkb1 As Workbook
Dim wkb2 As Workbook
Dim wks1 As Worksheet
Dim wks2 As Worksheet
Set wkb1 = ActiveWorkboook
Set wks1 = wkb1.Worksheets("Sheet1")
Set rng1 = wks1.Range("A1")
Set wkb2 = Workbooks("frmInputData.xls")
Set wks2 = wkb2.Worksheets("Sheet1")
Set rng2 = wks2.Range("A1:Z1")
For Each rng In rng2
If rng.Value = "A" Then
rng1.Value = 5
End If
Set rng1 = rng1.Offset(1,0)
Next rng

 

[kfguardian]

That works great, Thanks! Is there a way to do it without requiring the other
workbook to be open (that is frmInputData.xls)?

Also, I had to comment out the line "Set wkb1 = ActiveWorkboook." I think
it already assumes you are talking about the active sheet unless you say
otherwise?!

 

[Smallweed]

That was a typo on my part (too many "o"s!). It's safer to point to
everything explicitly I find but, yes, code assumes the active sheet in the
active workbook otherwise.

I don't think you can reference closed workbooks directly with VBA. One way
around this is to open the workbook with code (Workbooks.Open "path to file")
or I suppose you could create linking formulae in an already open sheet and
reference those in your VBA.

 

[kfguardian]

Oh- I didn't notice the extra 'o' either?!

Thanks for the information.