Need help evaluating cells in another workbook in macro
 

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.

Need help evaluating cells in another workbook in macro
 

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

Need help evaluating cells in another workbook in macro
 

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" wrote:

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

Need help evaluating cells in another workbook in macro
 

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.

Need help evaluating cells in another workbook in macro
 

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

Thanks for the information.

"Smallweed" wrote:

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.