Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Evaluating a group of cells | Excel Discussion (Misc queries) | |||
evaluating text of cells | Excel Discussion (Misc queries) | |||
Evaluating Workbook Content Impact on file Size | Excel Discussion (Misc queries) | |||
Evaluating Empty Cells | Excel Programming | |||
Evaluating Empty Cells | Excel Programming |