VBA request regarding simplification of code

[Nytekiss]

Hello VBA Superiors!

I am working with a workbook that uses a index/match formula with a few named ranges. my current code works just fine, but I would love to simplify it. I am aware I could put in a single code to drag the formula across the entire used range of the worksheet, but what I'm trying to avoid is the extensive time it takes to process everything. The audience I am preparing this for can be very impatient, and I would like to have a process that shows some sort of progressive results as it goes (unless there is actually a way to significantly cut the process time). Below is a sample of the code I am using. It starts with column B and continues on to column AQ.
Thank you to anyone taking the time to even glance at my novice VBA skills. I am here to learn.

Sub convert()
'
 Sheets("Reviews").Select
 Dim LastRow As Long
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
 
Range("B4").Select
    Selection.FormulaArray = _
        "=IFERROR(IF(INDEX(allofit,MATCH(1,(Name=RC1)*(SOP=R2C),0),7)=""Acquired"",""X"",IF(INDEX(allofit,MATCH(1,(Name=RC1)*(SOP=R2C),0),7)=""Overdue"",""O"",""--"")),"" "")"
    Selection.AutoFill Destination:=Range("B4:B" & LastRow)
    Range("B4:B" & LastRow).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Range("C4").Select
    Selection.FormulaArray = _
        "=IFERROR(IF(INDEX(allofit,MATCH(1,(Name=RC1)*(SOP=R2C),0),7)=""Acquired"",""X"",IF(INDEX(allofit,MATCH(1,(Name=RC1)*(SOP=R2C),0),7)=""Overdue"",""O"",""--"")),"" "")"
    Selection.AutoFill Destination:=Range("C4:C" & LastRow)
    Range("C4:C" & LastRow).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Range("d4").Select
    Selection.FormulaArray = _
        "=IFERROR(IF(INDEX(allofit,MATCH(1,(Name=RC1)*(SOP=R2C),0),7)=""Acquired"",""X"",IF(INDEX(allofit,MATCH(1,(Name=RC1)*(SOP=R2C),0),7)=""Overdue"",""O"",""--"")),"" "")"
    Selection.AutoFill Destination:=Range("d4:d" & LastRow)
    Range("d4:d" & LastRow).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

end sub

 

[jproffer]

I don't know about being any faster, but this is cleaner:

Sub test()

Sheets("Reviews").Select
 Dim LastRow As Long
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
 
    Range("B4").FormulaArray = _
        "=IFERROR(IF(INDEX(allofit,MATCH(1,(Name=RC1)*(SOP=R2C),0),7)=""Acquired"",""X"",IF(INDEX(allofit,MATCH(1,(Name=RC1)*(SOP=R2C),0),7)=""Overdue"",""O"",""--"")),"" "")"
    Range("B4").AutoFill Destination:=Range("B4:B" & LastRow)
    Range("B4:B" & LastRow).AutoFill Destination:=Range("B4:D" & LastRow)
    Range("B4:D" & LastRow).Value = Range("B4:D" & LastRow).Value

End Sub

Obviously, I don't have all the data you have so my testing was pretty limited, so PLEASE test that on a copy of your workbook...not the real deal.

To copy a range and paste values into the same range, you don't need to copy at all...see the last line in the code given.

HTH

 

[Nytekiss]

Hi jproffer!
Thank you for your reply. I used the fundamentals of your code to come up with this little trick.
took me a while to wrap my brain around using the integer and For/Next functions.. but I think I get it=)

Sub test()
Sheets("Reviews").Select
Dim LastRow As Long
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
Dim i As Integer
For i = 2 To 42
Cells(4, i).FormulaArray = _
        "=IFERROR(IF(INDEX(allofit,MATCH(1,(Name=RC1)*(SOP=R2C),0),7)=""Acquired"",""X"",IF(INDEX(allofit,MATCH(1,(Name=RC1)*(SOP=R2C),0),7)=""Overdue"",""O"",""--"")),"" "")"
Cells(4, i).AutoFill Destination:=Range(Cells(4, i), Cells(LastRow, i))
Range(Cells(4, i), Cells(LastRow, i)).Value = Range(Cells(4, i), Cells(LastRow, i)).Value
Next i

    
End Sub

thank you for the tip of not having to copy and paste - I'm trying to do as much as I can without using .select and that helps a lot!

 

[jproffer]

That will put that formula into every cell in row 4, from column B to...............whatever 42 is lol...which you no doubt know by now. Cells is just a touch different than Range in that the Row comes first rather than the Column, but they do basically the same thing, yes

A little more to wrap your head around the looping...you can add a step in there also.

 For i= 2 to 42 Step 2

would do every other column. You can also go backwards, which is handy if your deleting rows.

For i = LastRow to 2 Step -1

since deleting rows from the top down.......well it's not a great idea. Things get skipped...things you think should be gone...stay, things you think should have stayed are gone...it gets ugly lol.

EDIT TO ADD: I should have said, that will INITIALLY put that formula into row 4, columns B thru (whatever 42 is)...then you drag down on the next line. I saw it, just didn't comment on it