Building a formula using VBA

[almmohd]

Hi Everyone,

I need some help with a problem.

I have two spreadsheets, final_value.xls and source.xls (both in 2003 format).

I want to build a formula in final_value.xls which could be done using a vlookup formula if everything were simple. A sample of what the formula would be is shown in cells f6, g6 and h6 (F6=F2+M2+VLOOKUP(A6,'C:\Users\ALM\Desktop\[source.xls]Sheet1'!$A$3:$D$18,2,FALSE)) .

However, instead of having the vlookup formula in the cell I want the actual value in the formula as shown in f7, g7 and h7 (e.g. F7=F2+M2+52.41).

These formulas have to done throughout a range of cells in this case f6 to h21. Can anyone help with this?

[almmohd]

The source file is attached here.

[SamT]

almmohd,

The description of the formulas is hard to understand, I will write what I think, but you must correct me.

Notes: "Cell" means the Cell to receive the results of the VBA formula

Interpretation of VlookUp formula:
VLOOKUP(A6,[source.xls]Sheet1!$A$3:$D$18,4,FALSE)

Lookup_value is Cell.Row, Column A
table_array is A3 to D(LastRow)
Col_index_num can vary by monthInterpretation of SUM formula:
F6=F2+M2+VLOOKUP

Cell = F6
F2 = Row 2, Cell.Column
M2 = Relative to Cell How?

[almmohd]

You are correct in your interpretation of Cell and the vlookup formula.

In the SUM formula F2 is constant and M2, M3 and M4 depend on the three letter codes in Column A from row 6 to row 12.

so the formula would be
F+row = F2 + M2 + vlookup.value
(as shown in the cells highlighted green)

from row 13 to row 19 the formula is
F+row = F2 + M3 + vlookup.value

from row 20 to row 21 the formula is
F+row = F2 + M4 + vlookup.value

Similarly for JUN and JUL,
for JUN; G+row=G2+N2+vlookup.value
for JUL; H+row=H2+O2+vlookup.value

If a lookup value does not exist then the original formula in the cell can be left in place or no change to the contents of the cell is necessary.

I did F8 to F21 manually, that is what the VBA code should do, .

[SamT]

almmohd,

You can build UDF's to replace Vlookup and the Month reference in your formulas

The custom Vlookup would return 0 if the reference was not found so you could leave it in all cell formulas.

Month reference UDF:
Function RefMonCol()
'Dummy Function until we know what to refer to in function.
'Only returns Column 2 (May) for now.
RefMonCol = 2
End Function

Custom Vlookup function:
Function VLkp(RefCell As Range, RefMon As Long) As Double
Dim LastRow As Long
Dim SrcSht As Worksheet
Dim Result As Variant

'Test If Source Book is Open in Excel
On Error Resume Next
SrcSht = WorkBooks("Source.xls").Sheets("Sheet1")

'VLkp Returns 0 if Source.xls is not open. Prevents errors from showing on sheet.
If SourceSht Is Nothing Then
VLkp = 0
ExitFunction
End If

'Test is Good

LastRow = SrcSht.Cells(Cells.Count, 1).End(xlUp).Row

Result = VLOOKUP(RefCel.Value,SrcSht.Range("A3:$D" & LastRow,RefMon,FALSE)

'Test if VLookUp succeeded
If CStr(Result) = "#N/A " Then
VLkp = 0
Else 'Good answer
VLkp = CLng(Result)
End If


Put both those UDF's in Module1.

In a Cell formula where VLookUp is to return a value from Source Column May Replace The VLookUp Function with

VLkp(A1,RefMonCol) where "A1" is the correct row in Column A

I can not test it properly, so it might have small errors. Let me Know and when VLkp is working, we can work on RefMonCol and then a UDF to get the M2 type values.

[almmohd]

Something is wrong with this statement, it's showing up as red in the visual basic editor:
Result = VLOOKUP(RefCel.Value,SrcSht.Range("A3:$D" & LastRow,RefMon,False)

I think I may not have explained this properly and I may be looking at the problem wrong too. The spreadsheet exists already for one thing and the formulas exist already but are done manually. I don't want a vlookup formula showing up in the cells. The code in module1 kind of does what I need (looking at May only). I pieced it together using stuff I found on the internet, I'm not very good at visual basic.

My new problem is that different codes have formulas in column F that start differently, to get around this I will have to different do while loops. Could this have been programmed differently so that the program looks at the contents of the cell and changes only the value that has been looked up so that I do not have to specify the f2 + m3 in:

Sheet4.Range("f" & i).Formula = "=f2 + m3 + " & code1 & " * 0.42"

Or the f2+ m2 or the f2 + m4

And did I use the error checking correctly?


Sub FmlaBuilder()

i = 6

' range
Dim code1 As Variant

On Error Resume Next
Err.Clear

Do While Sheet4.Range("a" & i).Value <> "SXC"
code1 = Application.VLookup(Sheet4.Range("a" & i).Value, Sheet3.Range("a:d"), 3, False)
Sheet4.Range("f" & i).Formula = "=f2 + m2 + " & code1 & " * 0.42"
i = i + 1
Loop

Do While Sheet4.Range("a" & i).Value <> "BG5"
code1 = Application.VLookup(Sheet4.Range("a" & i).Value, Sheet3.Range("a:d"), 3, False)
Sheet4.Range("f" & i).Formula = "=f2 + m3 + " & code1 & " * 0.42"
i = i + 1
Loop
Do While Sheet4.Range("a" & i).Value <> "EK9"
code1 = Application.VLookup(Sheet4.Range("a" & i).Value, Sheet3.Range("a:d"), 3, False)
Sheet4.Range("f" & i).Formula = "=f2 + m4 + " & code1 & " * 0.42"
i = i + 1
Loop
End Sub



I have moved to lookup table to this spreadsheet for now but for the real problem I will have to look in another spreadsheet and I don't know how to do that as yet.

[almmohd]

is there any way to get this code to highlight the values of cells that it has updated?

[mdmackillop]

Add in this line where required
Sheet4.Range("f" & i).Interior.Colorindex = 6