Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: VBA Loop and convert cell to values until cell is blank
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Dec 2007
    Location
    Fort Worth Texas
    Posts
    78
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Loop and convert cell to values until cell is blank

    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

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,958
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA Loop and convert cell to values until cell is blank

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular
    Join Date
    Dec 2007
    Location
    Fort Worth Texas
    Posts
    78
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Loop and convert cell to values until cell is blank

    Quote Originally Posted by Fluff View Post
    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.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,958
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA Loop and convert cell to values until cell is blank

    Shouldn't be. Do you have any merged cells?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular
    Join Date
    Dec 2007
    Location
    Fort Worth Texas
    Posts
    78
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Loop and convert cell to values until cell is blank

    Quote Originally Posted by Fluff View Post
    Shouldn't be. Do you have any merged cells?
    no merged cells....but it is 180000 rows ...let me run it again...takes abouot 11.5 minutes
    thanks again!

    Cairo95

  6. #6
    Board Regular
    Join Date
    Dec 2007
    Location
    Fort Worth Texas
    Posts
    78
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Loop and convert cell to values until cell is blank

    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!

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,958
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA Loop and convert cell to values until cell is blank

    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?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #8
    Board Regular
    Join Date
    Dec 2007
    Location
    Fort Worth Texas
    Posts
    78
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Loop and convert cell to values until cell is blank

    here is a snap shot of the excel sheet

    [IMG]C:\Users\355343\Desktop\Sample 1.jpg[/IMG]

    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

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,958
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA Loop and convert cell to values until cell is blank

    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"
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  10. #10
    Board Regular
    Join Date
    Dec 2007
    Location
    Fort Worth Texas
    Posts
    78
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Loop and convert cell to values until cell is blank

    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •