VBA Replacing variable ranges that have values > 0 depending on cell value of the first column in row

Question

Okay, so I feel like I am getting closer but I am running in to an object error. I am trying to replace old values in an excel sheet with the new charge values. Here is an example of what I am trying to do.

This is an example of the type of table I might start out with.

This is what I want it to look like after I run the VBA

Here is what I have so far.

 Sub Testing()
  Dim x As Integer
  Dim UpdateRng As Range
  Dim SelectRng As Range
  v = 2

  Application.ScreenUpdating = False
  ' Get count
  NumRows = Range("B2", Range("B2").End(xlDown)).Rows.Count
  Range("B2").Select
  ' Cycle through loop
  For x = 1 To NumRows
  
    Set SelectRng = Range("C" & v & ":" & "F" & v) 'Set range
    
    If "A" & v.Vaule = " " Or v.Value = "" Then GoTo NextV
    
        For Each UpdateRng In SelectRng
            If UpdateRng.Value > 0 Then
                UpdateRng.Value = Range("A" & v).Value
            End If
        Next
NextV:
        v = v + 1
       Next
      Application.ScreenUpdating = True
End Sub

`v` is numeric and has no `.Value`. You should be using `x` in your loop. And `Range("A" & x).Value` — BigBen, Feb 16 '21 at 19:14
So any value in the right hand side has to be replaced with the value in the first column? Note too `v.Vaule`. You can dispense with the `Goto` too. — SJR, Feb 16 '21 at 19:16
Thank you for your help @BigBen I was able to get working with the v variable. I know I should be able to get it working with the x variable, but not sure how to get it to start on the right row. — SkysLastChance, Feb 16 '21 at 19:27
In future I would advise to have store types within a single column rather than across multiple columns (i.e. unpivot) — QHarr, Feb 16 '21 at 23:08

BigBen · Accepted Answer · 2021-02-16T20:06:45.847

Add Option Explicit to the top of the module and declare all variables.
Avoid using GoTo as that generally creates spaghetti code.
Use End(xlUp) to determine the last row.
Avoid using Select.
Use Long instead of Integer.

Sub Testing()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    
    With ws
        Dim lastRow As Long
        lastRow = .Cells(.Rows.Count, 2).End(xlUp).Row
    End With
    
    Dim i As Long
    For i = 2 To lastRow
        With ws
            If Not IsEmpty(.Range("A" & i).Value) Then
                .Range("C" & i & ":F" & i).Replace "*", .Range("A" & i).Value
            End If
        End With
    Next
End Sub

Note that this considers all values when replacing, not just values greater than 0. Though I think the >0 check is essentially checking if the cells in columns C:F are not empty.

That top part of your answer should be given, boilerplate, to all new users. — HackSlash, Feb 16 '21 at 19:38

score 0 · Answer 2 · answered Feb 16 '21 at 19:30

I got it working with this. However, Bigben's is much cleaner.

Sub Testing()
  Dim x As Integer
  Dim UpdateRng As Range
  Dim SelectRng As Range
  v = 2

  Application.ScreenUpdating = False
  ' Get count
  NumRows = Range("B2", Range("B2").End(xlDown)).Rows.Count
  Range("B2").Select
  ' Cycle through loop
  For x = 1 To NumRows
  
    Set SelectRng = Range("C" & v & ":" & "F" & v) 'Set range
    
    If Range("A" & v).Value = " " Or Range("A" & v).Value = "" Then GoTo NextV
    
        For Each UpdateRng In SelectRng
            If UpdateRng.Value > 0 Then
                UpdateRng.Value = Range("A" & v).Value
            End If
        Next

NextV:
        v = v + 1
       Next
      Application.ScreenUpdating = True
End Sub

VBA Replacing variable ranges that have values > 0 depending on cell value of the first column in row

2 Answers2