VBA help - probably easy to answer

[mtietz]

I am pretty new using VBA

I have a simple goal.

I want to use VBA to input a formula in every blank cell in column H only IF the cell in Column B on the same row has a number. If the cell in column H is not blank, I do not want the VBA to erase its contents, and if Column B is blank or has any text other than just a number, I don't want any changes to that row

Here is the formula
=IF(B11>0,INDEX(REPORT!A:B,MATCH(CONCATENATE("Char #",B11),REPORT!A:A,0)+1,2),"")

Thanks for any help!

 

[offthelip]

try this:

Sub test()
formstring = "=IF(B11>0,INDEX(REPORT!A:B,MATCH(CONCATENATE(""Char #"",B11),REPORT!A:A,0)+1,2),"""")"

LastRow = Range("A1").CurrentRegion.Rows.Count
inarr = Range(Cells(1, 1), Cells(LastRow, 8)) ' pick all data from column 1 to 8
For i = 1 To LastRow
 If IsNumeric(inarr(i, 2)) And (inarr(i, 8)) = "" Then
   Range(Cells(i, 8), Cells(i, 8)).Formula = formstring
 End If
 Next i

End Sub

 

[Peter_SSs]

try this:

Sub test()
formstring = "=IF(B11>0,INDEX(REPORT!A:B,MATCH(CONCATENATE(""Char #"",B11),REPORT!A:A,0)+1,2),"""")"

LastRow = Range("A1").CurrentRegion.Rows.Count
inarr = Range(Cells(1, 1), Cells(LastRow, 8)) ' pick all data from column 1 to 8
For i = 1 To LastRow
 If IsNumeric(inarr(i, 2)) And (inarr(i, 8)) = "" Then
   Range(Cells(i, 8), Cells(i, 8)).Formula = formstring
 End If
 Next i

End Sub

Some comments on that code:

• Range("A1").CurrentRegion may possibly give a smaller (or greater) range than is required to meet the specifications.
• IsNumeric returns True for blanks so the code would put a formula in if B & H are blank, contrary to the requirement
• I was also wondering about why the structure Range(Cells(i, 8), Cells(i, 8)).Formula rather than just Cells(i, 8).Formula?
• This is just a guess, but I suspect the B11 in the given formula is just an example and the 11 might be supposed to change depending on the row where the formula is being inserted.

Based on the above comments, my suggestion is this.
(I have assumed at least one blank cell in column H in the range covered by the column B data.)

Sub Insert_Formula()
  Dim c As Range
 
  For Each c In Range("H1:H" & Range("B" & Rows.Count).End(xlUp).Row).SpecialCells(xlBlanks)
    If Application.IsNumber(c.Offset(, -6).Value) Then
      c.Formula2R1C1 = "=IF(RC2>0,INDEX(REPORT!C1:C2,MATCH(CONCATENATE(""Char #"",RC2),REPORT!C1:C1,0)+1,2),"""")"
    End If
  Next c
End Sub