Forum Discussion

Copper Contributor

Jul 30, 2022

Solved

Macro VBA Excel - Conditioned copy of reference cell to a range of side cells

First, thanks for the help. I made a test macro for the first time to see if it worked and it was OK. However, it references a single cell due to the test. Now, I need to extend this macro to a ran...

Jul 30, 2022

Sub Copiar()
    Dim r As Long
    Dim n As Long
    Application.ScreenUpdating = False
    For r = 104 To 108
        n = Application.RoundUp(Range("S" & r).Value, 0)
        If n <= 0 Then
            n = 1
        ElseIf n > 6 Then
            n = 6
        End If
        Range("T" & r).Resize(1, n).Value = Range("S" & r).Value
    Next r
    Application.ScreenUpdating = True
End Sub

Remark: a line such as

If Range("S104") > 1 <= 2 Then

is not valid. It should be

If Range("S104") >1 And Range("S104") <= 2 Then

HansVogelaar

MVP

Jul 30, 2022

LeonelAFM

Sub Copiar()
    Dim r As Long
    Dim n As Long
    Application.ScreenUpdating = False
    For r = 104 To 108
        n = Application.RoundUp(Range("S" & r).Value, 0)
        If n <= 0 Then
            n = 1
        ElseIf n > 6 Then
            n = 6
        End If
        Range("T" & r).Resize(1, n).Value = Range("S" & r).Value
    Next r
    Application.ScreenUpdating = True
End Sub

Remark: a line such as

If Range("S104") > 1 <= 2 Then

is not valid. It should be

If Range("S104") >1 And Range("S104") <= 2 Then

LeonelAFM

Copper Contributor

Jul 31, 2022

Hi Hans!!!
It worked like a charm my friend.
Thank you very much for your prompt support!!

Since that was my first macro I still have some difficults on understanding some points.
For example: After I click "Refresh All" at "PivotChart Analyze" option, the macro does not work. So, I closed the code window and run it again and this time is return to working. Is that a normal condition? How could I get around the issue?

Thanks a lot!

HansVogelaar
MVP
Jul 31, 2022
LeonelAFM

That's strange - I don't see how refreshing pivottables would interfere with the macro...
- LeonelAFM
  Copper Contributor
  Aug 01, 2022
  HansVogelaar
  Hi Hans, me again!
  Excuse me but I had to ask:
  Weekly, I will have to add new rows to the this worksheet as shown.
  
  1) In this case, the macro would work as MS Excel adding this new row to it, automatically as a new range?
  2) And on the contrary, deleting a row, what would happen?
  
  3) If not automatic, could I insert a command to turn it automatically?
  
  Thanks for your patience and help.
  - HansVogelaar
    MVP
    Aug 01, 2022
    LeonelAFM
    
    Hi Leonel,
    
    The code currently handles only roes 104 to 108 (fixed).
    
    To make it more dynamic:
    
    Will the range always begin in row 104?
    
    Is the range to be processed the last used range on the sheet, or are there other non-empty rows below it?