VBA Loop and convert cell to values until cell is blank

cairo95

Board Regular
Joined
Dec 11, 2007
Messages
91
Hello,

i would like help in creating VBA code that will convert the 1st cell in the selected column...lets say column "E" and then every 4th cell after that from formulas to values until the last row.."180000" or when the next cell is blank. I found this code that works for me but you have to run it for each cell manually. if you could help construct a loop until it has gone through the column until the next cell is blank then exit.

Code:
Code:
Sub SelectEveryXRow()
'Created by Sumit Bansal at https://trumpexcel.com/ added code by cairo95
Dim MyRange As Range
Dim RowSelect As Range
Dim i As Integer
Set MyRange = Selection
Set RowSelect = MyRange.Rows(4)


For i = 4 To MyRange.Rows.Count Step 4
Set RowSelect = Union(RowSelect, MyRange.Rows(i))

Next i

Application.Goto RowSelect

' this is what i need for it to execute 

Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False



End Sub

Thank you so much!

Cairo95
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
How about
Code:
Sub cairo95()
   Dim i As Long
   
   For i = 4 To Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row Step 4
      Cells(i, ActiveCell.Column).Value = Cells(i, ActiveCell.Column).Value
   Next i
End Sub
 
Upvote 0
How about
Code:
Sub cairo95()
   Dim i As Long
   
   For i = 4 To Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row Step 4
      Cells(i, ActiveCell.Column).Value = Cells(i, ActiveCell.Column).Value
   Next i
End Sub

thank you! i ran this code and for some reason its converting every 13th cell...not sure why.
 
Upvote 0
Shouldn't be. Do you have any merged cells?
 
Upvote 0
here is a sample of what it is doing:

Code:
=IF(B1="","","WINCMD(C/"&TEXT(B1,"00000000")&")") ' the macro skipped the first line but correctly changed to value the 4th row...which is under the words"WINCMD(CER)"
WINCMD(SS/F/REFER TO ICARGO)
WINCMD(CER)

'converted this line correctly

=IF(B3="","","WINCMD(C/"&TEXT(B3,"00000000")&")") 'didnt convert this line...
WINCMD(SS/F/REFER TO ICARGO)
WINCMD(CER)
=IF(B4="","","WINCMD(C/"&TEXT(B4,"00000000")&")")' didnt convert this line
WINCMD(SS/F/REFER TO ICARGO)
WINCMD(CER)
=IF(B5="","","WINCMD(C/"&TEXT(B5,"00000000")&")") ' didnt convert this line
WINCMD(SS/F/REFER TO ICARGO)
WINCMD(CER)
'converted this line correctly


=IF(B7="","","WINCMD(C/"&TEXT(B7,"00000000")&")")
WINCMD(SS/F/REFER TO ICARGO)
WINCMD(CER)
=IF(B8="","","WINCMD(C/"&TEXT(B8,"00000000")&")")
WINCMD(SS/F/REFER TO ICARGO)
WINCMD(CER)
=IF(B9="","","WINCMD(C/"&TEXT(B9,"00000000")&")")
WINCMD(SS/F/REFER TO ICARGO)
WINCMD(CER)

thanks again!
 
Upvote 0
If I understand correctly that's every 3rd line not every 4th line.
However are you trying to convert all formulae in the column to values?
 
Upvote 0
here is a snap shot of the excel sheet



its my understanding that line 1,4,7 and so on should be converted ...when it is converted the 2 rows beneath if should vanish. the macro should skip over the cells that vanish due to the conversion.

thanks again!
cairo95
 
Upvote 0
I cannot see that image, because it's on your computer, you would have to post it to an image share site.
From what you posted in post#6 it looks like you have a formula every 3rd row with text in between.
If that's right I don't understand how converting a formula to a value would make the 2 rows of text "vanish"
 
Upvote 0
sorry im having issues uploading my snapshot so below should help understand what im seeing after running the macro you are helping with.
so, the macro should convert the 1 line then jump to the 4the and convert, this will make rows 5 and 6 go blank and so on...next would be row 7 to convert then rows 8 and 9 should go blank..hope this helps!

1 =IF(B1="","","WINCMD(C/"&TEXT(B1,"00000000")&")")
2 WINCMD(SS/F/REFER TO ICARGO)
3 WINCMD(CER)
4
5
6
7 =IF(B3="","","WINCMD(C/"&TEXT(B3,"00000000")&")")
8 WINCMD(SS/F/REFER TO ICARGO)
9 WINCMD(CER)
10 =IF(B4="","","WINCMD(C/"&TEXT(B4,"00000000")&")")
11 WINCMD(SS/F/REFER TO ICARGO)
12 WINCMD(CER)
13 =IF(B5="","","WINCMD(C/"&TEXT(B5,"00000000")&")")
14 WINCMD(SS/F/REFER TO ICARGO)
15 WINCMD(CER)
16
17
18
19 =IF(B7="","","WINCMD(C/"&TEXT(B7,"00000000")&")")
20 WINCMD(SS/F/REFER TO ICARGO)
21 WINCMD(CER)
22 =IF(B8="","","WINCMD(C/"&TEXT(B8,"00000000")&")")
23 WINCMD(SS/F/REFER TO ICARGO)
24 WINCMD(CER)


Thanks again!
Cairo95
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top