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 in Others by Kithuzzz
• 27,740 points
32 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 by narikkadan
• 51,240 points

Related Questions In Others

0 votes
1 answer
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
• 51,240 points
74 views
0 votes
1 answer

In a excel formula I need to create a list of names on one sheet based upon criteria/data of another sheet

The final formula is: =IF(ROWS($H$3:H3)<=$I$1,INDEX(Personnel! ...READ MORE

answered Nov 25, 2022 in Others by narikkadan
• 51,240 points
67 views
0 votes
1 answer

Get random value in the range of plus/minus 10% of a cell value in Excel

Why not just use RANDBETWEEN(B2*0.9, B2*1.1) if ...READ MORE

answered Dec 25, 2022 in Others by narikkadan
• 51,240 points
43 views
0 votes
1 answer

VBA Loop To Import Changing File Names

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

answered Jan 12 in Others by narikkadan
• 51,240 points
44 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 in Others by narikkadan
• 51,240 points
31 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 in Others by narikkadan
• 51,240 points
35 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
522 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 2 days ago in Others by narikkadan
• 51,240 points
20 views
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
• 51,240 points
126 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