rcfountain
04-07-2009, 05:06 AM
Greetings everyone,
I'm a bit new to Excel VBA programming and was wondering if I might have some assistance with a code snippet. I intended it to read through one column of a "source" spreadsheet, look up a corresponding value in another "destination" spreadsheet, and populate a seperate column in the "destination" if the value is found. Here is the code:
============
DestinationName = ActiveWorkbook.Name
For counter = 2 To 10000
Windows("Source.xlsm").Activate
' Store the two current columns in temp variables for comparison
temp_a_column = Worksheets(1).Range("A" & counter).Value
temp_b_column = Worksheets(1).Range("B" & counter).Value
' Look up corresponding value in destination spreadsheet
Windows(DestinationName).Activate
Set x = Worksheets(1).Range("c1:c10000").Find(temp_a_column)
' If the value is found, populate the K column in the destination spreadsheet
If Not x Is Nothing Then Set Worksheets(1).Range("k1:k10000").Cells(11, x.Row).Value = temp_b_column
Next counter
============
The problem is that the destination is not being populated. I placed some watch values in debug mode and found that the temp_a_column and temp_b_column variables are receiving values and that the .Find method is locating the appropriate value, but the "Set" statement is not functioning. Can someone point out to me what I need to be doing differently?
Thanks in advance,
Bob F.
I'm a bit new to Excel VBA programming and was wondering if I might have some assistance with a code snippet. I intended it to read through one column of a "source" spreadsheet, look up a corresponding value in another "destination" spreadsheet, and populate a seperate column in the "destination" if the value is found. Here is the code:
============
DestinationName = ActiveWorkbook.Name
For counter = 2 To 10000
Windows("Source.xlsm").Activate
' Store the two current columns in temp variables for comparison
temp_a_column = Worksheets(1).Range("A" & counter).Value
temp_b_column = Worksheets(1).Range("B" & counter).Value
' Look up corresponding value in destination spreadsheet
Windows(DestinationName).Activate
Set x = Worksheets(1).Range("c1:c10000").Find(temp_a_column)
' If the value is found, populate the K column in the destination spreadsheet
If Not x Is Nothing Then Set Worksheets(1).Range("k1:k10000").Cells(11, x.Row).Value = temp_b_column
Next counter
============
The problem is that the destination is not being populated. I placed some watch values in debug mode and found that the temp_a_column and temp_b_column variables are receiving values and that the .Find method is locating the appropriate value, but the "Set" statement is not functioning. Can someone point out to me what I need to be doing differently?
Thanks in advance,
Bob F.