Trying to run different macros based on value of cell in a range

0 votes

I'm attempting to create code that examines a value and, depending on it (OPGW or "Conductor"), executes either the OPGW macro or the Conductor macro in a cell further to the right. Then I want it to scroll down to the following line and repeat the process.

I want cell BG8 to execute the OPGW code if cell B8 is an OPGW cell, and cell BG9 to execute the Conductor code if cell B9 is a Conductor cell. The individual macros are not problematic, but it should be noticed that each macro is a very lengthy formula that SHOULD ONLY be executed in the active cell.

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
Jan 19, 2023 in Others by Kithuzzz
• 38,020 points
426 views

1 answer to this question.

0 votes

This demonstrates one approach to loop through a range and how to invoke another method depending on the value of each cell.

To be clear, you must provide the other subs with something to work with. Rather than relying on ActiveCell, you can pass a cell indirectly.

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
answered Jan 19, 2023 by narikkadan
• 63,680 points

Related Questions In Others

0 votes
1 answer

VBA - user input of row value to use in a cell range

Use this: Range("C" & c & ":I" & ...READ MORE

answered Feb 7, 2023 in Others by narikkadan
• 63,680 points
440 views
0 votes
1 answer
0 votes
1 answer

Excel Define a range based on a cell value

Let's say that cells A1, A2, A3, ...READ MORE

answered Nov 8, 2022 in Others by narikkadan
• 63,680 points
3,284 views
0 votes
1 answer

Is it possible to round to different decimal places in excel based on the range?

Where the range is multiple cells: Excel.Worksheet sheet ...READ MORE

answered Nov 17, 2022 in Others by narikkadan
• 63,680 points
662 views
0 votes
1 answer

VBA Loop To Import Changing File Names

You can use a FOR loop and ...READ MORE

answered Jan 12, 2023 in Others by narikkadan
• 63,680 points
843 views
0 votes
1 answer

How to add if this cell = 0 skip and go next

Try this: Option Explicit Sub AutoMakeInv() ...READ MORE

answered Jan 20, 2023 in Others by narikkadan
• 63,680 points
362 views
0 votes
1 answer

Automating production of graphs using VBA

You have to change: chrtname = Cells(i, 2).Value To: chrtname ...READ MORE

answered Jan 23, 2023 in Others by narikkadan
• 63,680 points
785 views
0 votes
1 answer

How can increase the speed of if statement in VBA Code?

Use a Dictionary Object. Option Explicit Sub PreencherO() ...READ MORE

answered Feb 7, 2023 in Others by narikkadan
• 63,680 points
517 views
0 votes
1 answer

Select a range in Excel based on the value of a cell

Try this: Dim WorkRng As Range Set WorkRng = ...READ MORE

answered Jan 29, 2023 in Others by narikkadan
• 63,680 points
714 views
0 votes
1 answer

VBA Loop to select then copy a range of cells based on value in column B

Try this: Sub Macro2() Dim ...READ MORE

answered Mar 23, 2023 in Others by narikkadan
• 63,680 points
2,257 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP