Consulting

Results 1 to 5 of 5

Thread: Repeat code for each row

  1. #1
    VBAX Regular
    Joined
    Dec 2017
    Posts
    10
    Location

    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
    Last edited by SamT; 01-11-2018 at 04:49 PM.

  2. #2
    VBAX Expert
    Joined
    May 2016
    Posts
    588
    Location
    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
    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,840
    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
    Please take the time to read the Forum FAQ

  4. #4
    VBAX Regular
    Joined
    Dec 2017
    Posts
    10
    Location
    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,840
    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"), ...
    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
  •