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:

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
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,278
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,127
Messages
5,526,998
Members
409,733
Latest member
revender17

This Week's Hot Topics

Top