Solved: Excel Macro Not Setting a Cell's Value

[rcfountain]

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.

[MikeBlackman]

Hi Bob,

I think this can be done with a VLOOKUP;


Sheets(1).Range("K1:K10000") = Application.VLookup(Sheets(1).Range("C1:C10000"), Workbooks("Source").Sheets(1).Columns("A:B"), 2, 0)

[Bob Phillips]

Can you give us an English, business focussed, summary ofg what you are trying to do?

[rcfountain]

Hi Bob,

I think this can be done with a VLOOKUP;


Sheets(1).Range("K1:K10000") = Application.VLookup(Sheets(1).Range("C1:C10000"), Workbooks("Source").Sheets(1).Columns("A:B"), 2, 0)
Thanks for the suggestion, Mike. Unfortunately, the destination row remains unpopulated. Most puzzling. Any other suggestions?


Can you give us an English, business focussed, summary ofg what you are trying to do?
Well, without going into any proprietary info, I think I can summarize it.

A system of ours produces a somewhat messy text file which contains the results of a test run of some data. The testers want us to verify their findings by matching up the text file's contents to the contents of a spreadsheet they maintain.

Up until now, we have been copying and pasting data from the text file into the spreadsheet, but as you can imagine, they would prefer an automated solution. Hence the code above.

So far, I've managed to get the text file's contents into a small (2-column) spreadsheet of its own. This is the "source" I mentioned; its column A is a field name, and column B is the field value. The big spreadsheet we've been manually pasting into is the "destination." The idea is to match up column A (variable temp_a_column) with a corresponding value in the C column of the destination (supposedly containing the corresponding field name). Once the match is made, the K column of the destination (field value) will be populated with the contents of column B of the source (variable temp_b_column).

Does that help?

[MikeBlackman]

Hi,

How about;


Sub Test()

Dim ActWb As Worksheet, TrgWb As Worksheet

Set ActWb = ActiveWorkbook.Sheets(1)
Set TrgWb = Workbooks("Source").Sheets(1)

On Error Resume Next

For i = 2 To TrgWb.Range("A" & Rows.count).End(xlUp).Row
ActWb.Columns("C").Find(TrgWb.Cells(i, 1)).Offset(0, 8) = TrgWb.Cells(i, 2)
Next i

End Sub

[rcfountain]

Thanks, Mike! That last code snippet did it with a minimum of adaptations. I appreciate the help. :)