Formula function to VBA Function

[MarkCBB]

Hi There VBA People,

I am currently using some VBA to insert this formula down 50,000 rows of data, then I copy the formulas and paste values. However I have been told that if i write the formula using proper VBA syntax it will calculate faster? and I wouldn't need to copy the results and paste them each time. Here is a sample

Sub Test()
    With Selection
        .FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-4],R1C3:R5C4,2,FALSE),"""")"
        .Copy
        .PasteSpecial Paste:=xlPasteValues
    End With
        Application.CutCopyMode = False
End Sub

How would this formula look written correctly in VBA?

 

[Peter_SSs]

I don't think there is anything wrong with your formula, but you don't need to copy/paste. Try this

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Test()<br>    <SPAN style="color:#00007F">With</SPAN> Selection<br>        .FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-4],R1C3:R5C4,2,FALSE),"""")"<br>        .Value = .Value<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

Note that you generally do not have to 'select' things in vba to work with them and selecting can slow your code. Is there any particular reason you are working with 'Selection'? Is there some way to identify which cells you want to populate with this code?

 

[MarkCBB]

Hi Peter,

Isnt there a way for the formula to calculate in VBA and just trasffer the Values? I know you code above does the same thing, I just heard the I can write the formula in VBA.

I have been told before that I should use Select or selection in VBA, But I am so used to it as I started learning VBA by watching what Macros would record.

The only way that I think I can identiy where the formula should go is by, is this what you mean by identifying the cells? Is the a better way to write the below code?

Sub Test2()
    Dim End1 As Range
    Set End1 = Range("H1048576").End(xlUp).Offset(1, 0)
   With End1
    .FormulaR1C1 = "=""MyFormula"""
   End With
End Sub

I tried using the .Value = value but whe i step though the code, the .value = value removes the value from the cell, example below, what I am doing wrong?

Sub Test2()
    Dim End1 As Range
    Set End1 = Range("H1048576").End(xlUp).Offset(1, 0)
    With End1
    .FormulaR1C1 = "=""MyFormula"""
    .Value = Value
    End With
End Sub

I would forward to your responce, thank you for your assistance so far.

Kindest Regards,
Mark Blackburn

 

[Norie]

Mark

You could perhaps use code to do the function but then you would probably need to loop through each cell to get the values for the function, calculate then place the result in the destination cell.

Somehow I don't know if that would be quicker.

Oh, and if you wanted to replace the entire formula with VBA so there are no worksheet functions involved you would need to create a VBA equivalent for
VLOOKUP.

 

[MarkCBB]

Hi Norie,

Just out of curisity what would the VBA code look like?

Peter, just another question on the Select/Selection topic, what is the correct way to writting this code?

 Range(Selection, Selection.End(xlDown)).Select

 

[Peter_SSs]

I tried using the .Value = value

You are missing a '.' - the code was

.Value = .Value

I'm not quite sure what you are saying about the location of the formula. However, if the formulas were going in column L, based on the number of rows in column H, then you could use something like this, without selecting anything.

With Range("L2:L" & Range("H" & Rows.Count).End(xlUp).Row)
    .FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-4],R1C3:R5C4,2,FALSE),"""")"
    .Value = .Value
End With

 

[Norie]

Mark

What would what code look like?

You really should try something like Peter has posted.

 

[MarkCBB]

Ahhh, that makes sense, thanks Peter.

Thanks Norie.