VBA to create/add formulae to a range of cells

[MrManBoy]

Gday All,

Trying to achieve: I have a list of formulae that I need to enter into a particular "Sheet" via VBA. The range is H2:X5000.
I have searched far and wide across the internet only to find solutions that do not yeild any results, literally for some reason.

Examples of some of the formulas being used:

• =IF($A2="","No Data Extracted",IF(ISNA(VLOOKUP($A2,HRData,1,FALSE)),"N","Y"))
  
• =IF($A2="","No Data Extracted",IF($H2="N","Y","N"))
• =IF($K2="Manual Check Required",$K2,EXACT($C2,$K2))
• =IF($A2="","No Data Extracted",IF($H2="N","Manual Check Required",VLOOKUP($A2,HRData,6,FALSE)))
• =IF($H2="N","Manual Check Required",IF($J2=TRUE,"Not Required",VLOOKUP($A2,HRData,6,FALSE)))

One example of code I did try for just column H was:

Dim lngLastRow As Long
    lngLastRow = Cells(Rows.Count, "H").End(xlUp).Row
    Range("H2:H5000" & lngLastRow).Formula = "=IF($A2="",""No Data Extracted"",IF(ISNA(VLOOKUP($A2,HRData,1,FALSE)),""No"",""Yes""))"

This did not work, nor did it cause any errors, yet if I remove the extra quotation marks from the formula, then I get a code error.

Well, hopefully the above made sense, as usual, if it didn't, let me what further info is needed

Appreciate any assistance given

 

[MrManBoy]

Hi

Looks like I have found a simple way per column whioch works great:

Sheets("Name of you sheet").Range("Your Range").Formula = "=IF($A2=" & Chr(34) & Chr(34) & ",""No Emp. #"",IF(ISNA(VLOOKUP($A2,HRData,1,FALSE)),""N"",""Y""))"

Thank you to anyone who was giving this a shot, by all means if you find a quicker way, to my original query, by all means let us know - Cheers

 

[Peter_SSs]

One example of code I did try for just column H was:

Dim lngLastRow As Long
    lngLastRow = Cells(Rows.Count, "H").End(xlUp).Row
    Range("H2:H[COLOR="#FF0000"][B]5000[/B][/COLOR]" & lngLastRow).Formula = "=IF($A2="",""No Data Extracted"",IF(ISNA(VLOOKUP($A2,HRData,1,FALSE)),""No"",""Yes""))"

This did not work, nor did it cause any errors, yet if I remove the extra quotation marks from the formula, then I get a code error.

There are two fairly obvious problems with that code.

1. We don't know exactly what value the variable lngLastRow holds but I suspect that red 5000 should not be there as lngLastRow seems to be what should identify the last row of the range.

2. You don't have enough extra quotation marks.

3. One other possible issue. Are you sure it is column H that determines how far down the formula should go? That is, is this code putting these formulas over the top of something that is already there or should you be using another column to determine the last row?

Anyway, try this

lngLastRow = Cells(Rows.Count, "H").End(xlUp).Row
Range("H2:H" & lngLastRow).Formula = "=IF($A2="""",""No Data Extracted"",IF(ISNA(VLOOKUP($A2,HRData,1,FALSE)),""No"",""Yes""))"

 

[MrManBoy]

Thanks Peter - Appreciate that, I will be sure to give that a go.

In regards to point #3, you are right in that case, as there is nothing in column H, there is nothing in there to determine the last row, if I understand you correctly. If so, that would mean for me to change the "H" in Cells(Rows.Count, "A"), as this would better determine the last row to count up from. Let me know if I got that right please.

 

[Peter_SSs]

If so, that would mean for me to change the "H" in Cells(Rows.Count, "A"), as this would better determine the last row to count up from. Let me know if I got that right please.

Yes, exactly. "A" or whatever column is sure to have data down to where you want the formulas in column H to go.

 

[MrManBoy]

Thanks Peter! - For clearing that up and explaining it as well, and the code, this is exactly what I am trying to achieve