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.