SolvedPrivate

VBA Macro Excel_ Like String

Posted on 2015-02-10
1
21 Views
Last Modified: 2016-02-10
How do I use a like term if my string my vary but always starts Profile:  ConditionString = "Profile: Physician Profile-Surgery"

I have several of these, but it would be for Surgery, Medicine, etc.

Sub CopyOnString()
Dim ConditionString As String, MasterSheetName, NewSheetName
Dim LastRow As Integer, ii, FindCount, StartRange, EndRange
MasterSheetName = "Profile Export"
Worksheets(MasterSheetName).Activate
FindCount = 0
StartRange = 0
EndRange = 0
NewSheetName = MasterSheetName
ConditionString = "Profile: Physician Profile-Surgery"
LastRow = Worksheets(MasterSheetName).UsedRange.Rows.Count
 For ii = 1 To LastRow
  If Worksheets(MasterSheetName).Cells(ii, 1) = ConditionString Or ii = LastRow Then
    If FindCount > 0 Then ' not first find
    EndRange = ii - 1
    Worksheets.Add , Worksheets(NewSheetName)
   
    DoEvents
    NewSheetName = Split(Worksheets(MasterSheetName).Cells(StartRange + 1, 1).Value, ":", 2)(1)
    ActiveSheet.Name = NewSheetName
    DoEvents
    Worksheets(MasterSheetName).Range(LTrim(CStr(StartRange)) & ":" & LTrim(CStr(EndRange))).Copy _
    Destination:=Worksheets(NewSheetName).Range("1:1")
    Worksheets(NewSheetName).Columns("A:I").AutoFit
    End If
    FindCount = FindCount + 1
    StartRange = ii
    EndRange = 0
   
  End If
 Next
End Sub
0
Comment
Question by:slatefamily
1 Comment
 

Accepted Solution

by:
slatefamily earned 0 total points
ID: 40601647
I got it to work by this:
 If Worksheets(MasterSheetName).Cells(ii, 1) = ConditionString Or ii = LastRow And InStr(Cells(ii, 1), ConditionString) > 0 Or ii = LastRow Then
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This is an Add-On procedure to be used in conjunction with the code provided in Reducing EE Email Clutter using Outlook (http://www.experts-exchange.com/Software/Office_Productivity/Groupware/Outlook/A_3146-Outlook-Processing-EE-emails-on-Receive.…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Viewers will learn the different options available in the Backstage view in Excel 2013.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now