Fixing a Vlookup emulation macro

[sheetspread]

MrKowz graciously wrote this for me last year and I've tried to solve the minor glitch that produces zeroes instead of the multiplication formula.

Excel Workbook
	D	E	F
1	Color	Friday	Saturday
2	Red	0.3	0.7
3	Orange	0.5	0.5
4	Yellow	0.1	0.9
5	Green	0.2	0.8
6	Blue	0.4	0.6
Allocation

Is the allocation tab

Excel Workbook
	C	D	E	F
11	Number	Color	Friday	Saturday
12	34	Orange
13	12	Blue
14	76	Yellow
15	8	Green
Destination

Is the destination tab

Now the destination tab should look like this if the "vlookupless" macro works:

Excel Workbook
	C	D	E	F
11	Number	Color	Friday	Saturday
12	34	Orange	17	17
13	12	Blue	4.8	7.2
14	76	Yellow	7.6	68.4
15	8	Green	1.6	6.4
Destination

But, after using this macro:

Option Explicit
Public Sub LookupEmulate()
' ** Declare variables
Dim i As Long, j As Long, lastrow1 As Long, lastrow2 As Long
Dim lookupvalue As String
Dim fridayresult As Double, saturdayresult As Double
Dim ws1 As Worksheet, ws2 As Worksheet
' ** Adjust sheet names accordingly
Set ws1 = Sheets("Allocation")
Set ws2 = Sheets("Destination")
lastrow1 = ws1.Range("D" & Rows.Count).End(xlUp).Row
lastrow2 = ws2.Range("D" & Rows.Count).End(xlUp).Row
' ** Loop to find the lookup value and return the multipliers
' ** This also populates columns E and I on the destination sheet
' ** Adjust beginning of For loops to fit your data
' ** Also adjust column references to fit your data
For i = 12 To lastrow2
lookupvalue = ws2.Range("D" & i).Value
For j = 2 To lastrow1
If ws1.Range("D" & j).Value = lookupvalue Then
fridayresult = Range("E" & j).Value
saturdayresult = Range("F" & j).Value
Exit For
End If
Next j
ws2.Range("E" & i).Value = Range("C" & i).Value * fridayresult
ws2.Range("F" & i).Value = Range("C" & i).Value * saturdayresult
Next i
End Sub


I get this:

Excel Workbook
	C	D	E	F
11	Number	Color	Friday	Saturday
12	34	Orange	0	0
13	12	Blue	0	0
14	76	Yellow	0	0
15	8	Green	0	0
Destination

So what part of the code is printing zeroes instead of the multiplication of the allocation and destination cells?

 

[sheetspread]

Is the problem:

ws2.Range("E" & i).Value = Range("C" & i).Value * fridayresult
ws2.Range("F" & i).Value = Range("C" & i).Value * saturdayresult

???

I don't know VBA well enough to see why it would or wouldn't work. Does .Value refer to a number or reference? I'd like the cells to be filled with one reference times another reference.

 

[kpark91]

Hi, sheetspread.

ws2.Range("E" & i).Value = Range("C" & i).Value * fridayresult
 ws2.Range("F" & i).Value = Range("C" & i).Value * saturdayresult

is indeed the code that produces 0 or any other values.

Try fixing it to

ws2.Range("E" & i).Value = ws2.Range("C" & i).Value * fridayresult
 ws2.Range("F" & i).Value = ws2.Range("C" & i).Value * saturdayresult

and

fridayresult = Range("E" & j).Value
 saturdayresult = Range("F" & j).Value

to

fridayresult = ws1.Range("E" & j).Value
 saturdayresult = ws1.Range("F" & j).Value

 

[sheetspread]

So close kpark, how do I print formulas instead of values?

 

[kpark91]

Instead of .Value, replace it with .Formula

 

[sheetspread]

Didn't work, I tried changing all .Value to .Formula and then figured that the lookup part should stay as .Value but neither gave me the product of the cell references. Is there a .Reference?

 

[sheetspread]

Finally............After using what little I know to modify the code, it works. Here's how it goes:

Excel Workbook
	D	E	F
1	Color	Friday	Saturday
2	Red	0.3	0.7
3	Orange	0.5	0.5
4	Yellow	0.1	0.9
5	Green	0.2	0.8
6	Blue	0.4	0.6
Allocation

Excel Workbook
	C	D	E	F
11	Number	Color	Friday	Saturday
12	34	Orange
13	12	Blue
14	76	Yellow
15	8	Green
Destination

Run this code:

Option Explicit
Public Sub LookupEmulate()
' ** Declare variables
Dim i As Long, j As Long, lastrow1 As Long, lastrow2 As Long
Dim lookupvalue As String
Dim fridayresult As String, saturdayresult As String
Dim ws1 As Worksheet, ws2 As Worksheet
' ** Adjust sheet names accordingly
Set ws1 = Sheets("Allocation")
Set ws2 = Sheets("Destination")
lastrow1 = ws1.Range("D" & Rows.Count).End(xlUp).Row
lastrow2 = ws2.Range("D" & Rows.Count).End(xlUp).Row
' ** Loop to find the lookup value and return the multipliers
' ** This also populates columns E and I on the destination sheet
' ** Adjust beginning of For loops to fit your data
' ** Also adjust column references to fit your data
For i = 12 To lastrow2
lookupvalue = ws2.Range("D" & i).Value
For j = 2 To lastrow1
If ws1.Range("D" & j).Value = lookupvalue Then
fridayresult = ws1.Range("E" & j).Address
saturdayresult = ws1.Range("F" & j).Address
Exit For
End If
Next j
ws2.Range("E" & i).Formula = "=" & ws2.Range("C" & i).Address & "*" & "Allocation!" & fridayresult
ws2.Range("F" & i).Formula = "=" & ws2.Range("C" & i).Address & "*" & "Allocation!" & saturdayresult
Next i
End Sub

And magically appearing:

Excel Workbook
	C	D	E	F
11	Number	Color	Friday	Saturday
12	34	Orange	17	17
13	12	Blue	4.8	7.2
14	76	Yellow	7.6	68.4
15	8	Green	1.6	6.4
Destination