If a condition is met convert range to value from formula

[Mothergreen]

Good Afternoon All

First I just want to thank everyone on here regardless of solution. I have been following this board for a while and this site has always helped me expand my knowledge of VBA. I am no expert by any means but i now enough to get myself into trouble.

Image if you will of a spreadsheet where in column A you have a drop down with two options to pick from open and close.

Columns B - E have formulas in them that pull from another sheet (specifically vlookup formulas)

What i am trying to accomplish is this. If a person selects close from the drop down in column A, columns (B-E), on that row, gets converted from the vlookup formula to its value.

I have tried writing this a number of way but either excel says that there is a problem with the code or i get the wrong result. I just can't seem to get it to specify to the specific rows that meet the condition. This is the closest working code that i have so far:

Sub Celladjust()

 Dim rng As Range
    
   Set rng = Range("A:A")
   For Each Cell In rng
       If Cell.Value = "close" Then
    Columns("B:E").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End If
    Next
End Sub

I have tried specifying range for rows but I am doing something wrong.

If someone can help point me in the right direction with this that would be amazing.
Thank you all.
MG

[humdingaling]

Post withdrawn

[Mothergreen]

Adjusted sorry about that. First time posting code on the forum

[gmr4evr1]

The only thing I did was add

Dim cell as Range

to your code and it seemed to work

[Mothergreen]

I did that as well. Specifying the range and for it to specifically affect only the rows that meet the requirements still eludes me. Its either all or nothing when i specify a range either a excel says something is wrong with the range or at the first close it goes through the whole spreadsheet and changes all the formulas in rows B-E

[humdingaling]

not a problem

possible solution you can use below

Sub CellAdjust()
    
    Dim i As Long
    
    For i = 1 To ActiveSheet.UsedRange.Rows.Count
    
        If Cells(i, 1) = "Close" Then
            Range(Cells(i, 2), Cells(i, 5)).Value = Range(Cells(i, 2), Cells(i, 5)).Value
        End If

    Next
End Sub

Mock up file attached

[Mothergreen]

Thank You Humdingaling this worked. This looks much simpler then what i was doing. I guess i have spent to much time learning from recording macros instead of branching out. I am going to try this code on a similar sheet that goes on for about 3000+ rows fingers crossed .

[JOHN H. DAVIS]

FWIW:

Sub Mothergreen()
 Dim rng As Range
 With Application
    .DisplayAlerts = False
    .ScreenUpdating = False
    .Calculation = xlManual
End With
   Set rng = Range("A:A")
   For Each cell In rng
       If cell.Value = "close" Then
            With Range(Cells(cell.row, "B"), Cells(cell.row, "E"))
                .Value = .Value
            End With
    End If
    Next
With Application
    .DisplayAlerts = True
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
End With
End Sub

[humdingaling]

just to explain my code further

the "i" in my code represents the "current" row

For i = 1 To ActiveSheet.UsedRange.Rows.Count

so it starts from row one (i = 1) and loops until "ActiveSheet.UsedRange.Rows.Count" which should represent your last row
this helps a bit in the code running faster so it doesnt loop for million+ rows

syntax for cells (rows,columns)

so this line

If Cells(i, 1) = "Close" Then

looks at column 1 which is A

i = 1 then a1, i = 2 then a2, i = 3 then a3....etc

rest of the code is self explanatory

Range(Cells(i, 2), Cells(i, 5)).Value = Range(Cells(i, 2), Cells(i, 5)).Value

this just copies the value and replaces with value ...its another way of doing pastespecial - values

[Mothergreen]

Thank you all for your time and help. This is exactly what i needed and thank you for the amazingly quick reply. You all make this board awesome.