0

I am trying to write code that looks at a value, and based on the value ("OPGW" or "Conductor") runs either the OPGW macro or the Conductor macro in another cell further to the right. Then I want it to move down to the next line and do it all again.

So if Cell B8 is OPGW, I want Cell BG8 to run the OPGW code, and then if cell B9 is Conductor, I want cell BG9 to run the Conductor code. There's no problem with the individual macros, though it should be noted that each macro is an exceedingly long formula that SHOULD only take place in the active cell. The only problem I'm having is that it won't go down to the next row and do it all again.

Sub WireUpdate()
N = Cells(Rows.Count, "A").End(xlUp).Row
CR = ActiveCell.Row()

With Range("BG8:BG" & N)
  If ActiveSheet.Cells(CR, 2) = "OPGW" Then
     Call OPGW2
   Else: Call Conductor2
     End If
End With

Thank you so much for your help.

I have tried the following code

With Range("BG8:BG" & N)
If ActiveSheet.Cells(CR, 2) = "OPGW" Then
Call OPGW2
Else
    If ActiveSheet.Cells(CR, 2) = "Conductor" Then
    Call Conductor2
    Else: ActiveSheet.Cells(CR, 2) = "0"
    End If
End If
End With

I have also tried to make the OPGW and Conductor macros loop, which works, but tends to overwrite the other data. So, it will put in all the OPGW things in, then go through and put all the Conductor things in, overwriting the OPGW things with 0.

I also checked out the following article: Running Different Macros Based on Values in Range but it didn't seem to be what I need, unless it can be adapted in a way I haven't fathomed yet.

KACB
  • 1
  • 1
  • 1
    Sounds like you want a loop? – BigBen Jan 18 '23 at 17:15
  • Thought that's what my With statement was. Is there a better way? – KACB Jan 18 '23 at 17:36
  • 1
    `With` is not a loop. You might consider an internet search for VBA loops. – BigBen Jan 18 '23 at 17:41
  • Articles that may help: [With Statement Usage](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/with-statement) - [Do While/Until ... Loop Usage](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/doloop-statement) - [For Loop Usage](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/fornext-statement) - ["For Each" Loop Usage](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/for-eachnext-statement) – Cameron Critchlow Jan 19 '23 at 00:51

1 Answers1

0

This shows one way to loop over a range, and how to call another method based on each cell's value.

Note you need to give the other subs something to work with - you can pass a cell in directly, rather than relying on something like ActiveCell

Sub WireUpdate()
    Dim ws As Worksheet, c As Range, n As Long
    
    n = ws.Cells(Rows.Count, "A").End(xlUp).Row
    For Each c In ws.Range("BG8:BG" & n).Cells
        Select Case c.Value
            Case "OPGW": OPGW c     '<< call method and pass in the cell
            Case Else: Conductor2 c
         End Select
    Next c
End Sub

Sub OPGW(c As Range)
    'do something with c
End Sub

Sub Conductor2(c As Range)
    'do something with c
End Sub
Tim Williams
  • 154,628
  • 8
  • 97
  • 125