Title: Minor adjustment to VBA Module to eliminate redudancy

KP_SoCal

Board Regular
Joined
Nov 17, 2009
Messages
116
Instead of this (from code at the bottom of thread):
Code:
FinalTweeks2 targetRange, "GroupA"
FinalTweeks2 targetRange, "GroupB"
FinalTweeks2 targetRange, "GroupC"
I would like to do something like this:
Code:
FinalTweeks2 targetRange, left("Group,5")
Obviously my attempt does not work, but would appreciate any suggestions. Thanks!


Code:
Sub FinalTweeks1()

Dim targetRange As Range
' change this to where you want to work on
Set targetRange = ActiveSheet.Range("A:A")
FinalTweeks2 targetRange, "CategoryA"
FinalTweeks2 targetRange, "GroupA"
FinalTweeks2 targetRange, "GroupB"
FinalTweeks2 targetRange, "GroupC"
End Sub

Sub FinalTweeks2(targetRange As Range, what As String)

Dim found As Range, first As Range
Set first = targetRange.Find(what, After:=Range("A" & Rows.Count), LookIn:=xlValues, LookAt:=xlWhole)
If Not first Is Nothing Then
first.Resize(2, 1).EntireRow.Insert 'inserts two rows above "first"

    Set found = targetRange.FindNext(first)
    Do While (Not found Is Nothing)
        If (found.Address = first.Address) Then Exit Do
         'found.Clear  '''''clears the cell found
         'found.EntireRow.Clear
         'found.EntireColumn.Clear
         found.Resize(, 20).Clear  '''''changes the size of the range A:E
         
        Set found = targetRange.FindNext(found)
    Loop
End If
End Sub
 
Last edited:

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,343
Office Version
  1. 365
Platform
  1. Windows
How doesn't it work?

Just with a quick glance it's not obvious, apart from left("Group,5").

If you want to get the 1st 5 characters try Left("Group,5",5).

If you want to do something else post an explanation of what it is.:)
 

KP_SoCal

Board Regular
Joined
Nov 17, 2009
Messages
116
Sorry, I didn't do a very good job explaining this. Basically, I'm passing 5 different variables through FinalTweeks2. When the string of variables starts with "Group", I'm trying to avoid all the additional lines of code by having to key in:

FinalTweeks2 targetRange, "GroupA"
FinalTweeks2 targetRange, "GroupB"
FinalTweeks2 targetRange, "GroupC"

Instead of just something like, FinalTweeks2 targetRange, like "Group%"

Does this make more sense?
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,450
That makes sense, but do you want to run FinalTweeks2 for all Groups e.g. "Group*" or do you want to Run it for 5 specific groups Groups A-E and there could be additional Groups besides the specific 5?

The reason I ask is that you could change FinalTweeks2 to run on all groups or you could send it the names of 5 groups?

This is one way you could loop FinalTweeks2 five times for five specific groups
Code:
Sub FinalTweeks1()

    Dim targetRange As Range, grp As Variant
    ' change this to where you want to work on
    Set targetRange = ActiveSheet.Range("A:A")
    For Each grp In Array("GroupA", "GroupB", "GroupC", "GroupD", "GroupE")
        FinalTweeks2 targetRange, CStr(grp)
    Next grp
End Sub
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,712
Messages
5,833,267
Members
430,200
Latest member
ADLHMA2022

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top