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

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?
  - LeonelAFM
    Copper Contributor
    Aug 01, 2022
    Hi Hans!
    1) Will the range always begin in row 104?
    > No, it will start in row 8.
    
    2) Is the range to be processed the last used range on the sheet, or are there other non-empty rows below it?
    > There are other non-empty rows below it. Due they have some fixed values instead to be calculated.
    
    Thanks Hans!