Forum Discussion

LeonelAFM's avatar
LeonelAFM
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 range of cells, where each reference cell has a different condition (column "S"), which generates a filling of a certain number of cells depending on the condition of the referenced cell.

In the figure below, I show the worksheet which I intend to apply the macro. Reference cells are in red and the horizontal ranges of cells to be filled (copied) in are in purple.

 

The code is below:

1) Read the reference cell

2) Compare its values range

3) Copy the value in reference cell into the range of cells as determined by the "IF" conditional

 

Thanks a lot for your help!!

excel
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    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

16 Replies

  • HansVogelaar's avatar
    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's avatar
      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!

Resources