[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
SolvedPrivate

VBA Macro Excel_ Like String

Posted on 2015-02-10
1
Medium Priority
?
27 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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 …
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

656 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