Consulting

Results 1 to 5 of 5

Thread: Repeat code for each row

  1. #1

    Repeat code for each row

    I am new at working with VBA and have found online some VBA code to copy cell range value from a formula then delete the formula and paste the value in the cells.

    And it works fine but i would like it to do now is if Cell B10 ="L" to run the VBA code and also repeat the code for each row. my current last row is 352

    Sub Copy_Paste_Remove_Formula() 
        Dim smallrng As Range 
        For Each smallrng In Sheets("Job Quote Hours").Range("j10:EO10").Areas 
             
            With smallrng 
                .Cells.Copy 
                .Cells.PasteSpecial xlPasteValues 
                .Cells(1).Select 
            End With 
            Application.CutCopyMode = False 
        Next smallrng 
         
        For Each smallrng In Sheets("Job Quote Hours").Range("J10:EO10").Areas 
             
            With smallrng 
                .Value = .Value 
            End With 
        Next smallrng 
    End Sub 
    
    
    Formatting tags added by mark007
    Last edited by SamT; 01-11-2018 at 04:49 PM.

  2. #2
    Try this not tested because i am on an android tablet
    inarr=workSheets("Job Quote Hours").Range("J10:EO352") 
    Worksheets("Job Quote Hours").Range("J10:EO352")=inarr 
    
    
    Formatting tags added by mark007
    Sam has got there first, it is really slow trying to write vba on a tablet

  3. #3
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,311
    Location
    Sub VBA_SamT() 
        Dim Rng As Range 
         
        With Sheets(""Job Quote Hours") 
            Set Rng = Range(.Range("j10:), .Cells(Rows.Count, "EO").End(xlUp)) 
            If .Range("B10") = "L" Then 
                Rng.Value = Rng.Value 
            End If 
        End With 
    End Sub 
    
    
    Formatting tags added by mark007
    Please take the time to read the Forum FAQ

  4. #4
    Sam,

    I have to appoligize for being new to this but i am not sure where to put the code you wrote.

    also it looked like there is some type of error in 3rd and 4th line.

    i appriciate you help

    Jeffrey

  5. #5
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,311
    Location
    where to put the code you wrote.
    Personally? I would put it in a Standard Module. It could go in the ThisWorkbook Code Page, but I prefer to only put code that affects the entire workbook in ThisWorkbook. Even, if it's called from a UserForm, in that UserForm's Code module.

    looked like there is some type of error in 3rd and 4th line.
    Good Eye
     
    With Sheets("Job Quote Hours") 
        Set Rng = Range(.Range("j10"), ... 
    
    
    Formatting tags added by mark007
    Please take the time to read the Forum FAQ

Posting Permissions

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