Instr and Mid statements to find/replace data from 1 wksht to another 1

[scpankow]

I have one worksheet that has four columns, Description, Category, New Number, Old Number. I have a second worksheet that has four columns, Sec. Reg. No., HIPAA Req., Doc. Type, and Doc. Name/Description. I need to search the data in the Doc. Name/Description field for Old Numbers and replace them with the corresponding New Numbers from the first sheet. I believe that I need to use Instr and Mid, but I am a VBA newbie and not sure how to do so. Any and all help would be greatly appreciated.

Thanks,

Shannon

 

[SkipVought]

First, it would be helpful is you posted a sample of what data you are searching for and replacing.

Have you TRIED any code yet? Please post any code you are working with.

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[SkipVought]

Have you tried using you macro recorder, when doing the search and replace?

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[scpankow]

No, I have not tried that. When I say "newbie" I am being serious. I have had no classes, I am just learning on my own.
I will try and see what I come up with and post what I get.
Thanks,
Shannon

 

[scpankow]

Ok, here is what I have so far, please don't laugh....


Sub GuidelinesFindReplace()
'
' GuidelinesFindReplace Macro
'
Windows("HIPAA One Page Wonder_05012006.xls").Activate
Cells.Select
Range("A1:E58").Activate

Dim oldGuidelines As String

Dim newGuidelines As String


InStr([E3], [oldGuidelines], [newGuidelines], [Compare As vbTextCompare]) = 1


End Sub

How do I set the String oldGuidelines equal to the range of data I want?

shannon

 

[SkipVought]

We have ALL been newbies. Most of us have never had a class in Excel or VBA. But we have been mentored along the way by many.

An important part of the mentoring process, is getting pointed in the right direction and being given a hint or two. THEN going off and working it out. Come back with a result or a problem and the process happens again.

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[AnotherHiggins]

shannon,

Skip mentioned the Macro Recorder. You can use that by (from Excel) going to Tools > Macro > Record New Macro.

Then just do whatever you want in excel.

When you're done, view the code that was generated. It is sometimes needlessly complex, but we can help you to clean it up.

To make sure I understand what you're trying to do:

You have a single field that contains both the doc's name AND description? And you want to look at just the description part to see if any of them need to be replaced with new descriptions, right?

I'd advise you to split Doc Name and Doc Description into two different columns.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

I'd advise you to split Doc Name and Doc Description into two different columns.

Check out Data > Text to columns... Foggure out HOW to parse. then macro record actually doing it.

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[scpankow]

Well, they kind of go together. For example:

IM-GEN-G0004 - Local and Wide Area Network Security

This is the type of data contained in these fields. I need to search these fields for the old guideline #'s (IM-GEN-G0004) and replace them with the new guideline #'s which is contained on the second sheet.
Here is what i have so far...
Function GuidelinesFindReplace(searchVal As String, srchRng As Range, startCell As Range)
Dim srchRng As Range
Set srchRng = Range("E3:E58").Find(What:="oldGuideline", _
LookIn:="HIPAA One Page Wonder_05012006.xls")

Windows("HIPAA One Page Wonder_05012006.xls").Activate
Cells.Select
Range("A1:E58").Activate

Dim oldGuideline As String
Set oldGuideline = **what do I put here?**
Dim newGuideline As String
Set newGuideline = **and here?**

If srchRng Is Nothing Then
MsgBox "Data not found"
Exit Function
Else

InStr([E3], [oldGuideline], [newGuideline], [Compare As vbTextCompare]) = 1

End Function

Shannon

 

[SkipVought]

Have you tried Edit > Replace... on the sheet???

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[Bong]

As Skip has said above, it would be helpful to see some sample of your data. That said, here are some pointers:

Let's start with 2 open workbooks, say, "abc.xls" and "xyz.xls". I don't know why the windows collection is appealing to you; I would use the workbooks collection:

set wb1 = workbooks("abc.xls")
set wb2 = workbooks("xyz.xls")

Now let's say they both have only 1 worksheet each: abc.xls has a sheet named "zaphod", and xyz.xls has a sheet named "marvin":

set s1 = wb1.sheets("zaphod")
set s2 = wb2.sheets("marvin")

You can probably deduce that this could be done in one step:

set s1 = workbooks("abc.xls").sheets("zaphod)
set s2 = workbooks(xyz.xls").sheets("marvin")

Now, on sheet, s1, you have in row 1 a series of column headers, one of which is "Doc. Name/Description". So we can search row 1 in s1 for that and get the column index:

intCx = s1.rows(1).Find("Doc. Name/Description").column

Likewise, on sheet, s2, there's a column headed "Old Number", whose column index is:

intCy = s2.rows(1).Find("Old Number").column

Now here is where I'd need to see what the data look like in order to proceed. Let's just note that s1.columns(intCx) is a (searchable) range, as is s2.columns(intCy).

_________________
Bob Rashkin

 

[scpankow]

Bob,
Ok, I am following you. That really helps. Here is what I have so far. Are they in the right order?

Shannon


Function GuidelinesFindReplace()

Dim srchRng As Range

Set srchRng = Range("s1.columns(intCx)")
Set srchValue = Range("s2.columns(intCx)")

Set s1 = Workbooks("HIPAA One Page Wonder_05012006.xls").Sheets("IM")
Set s2 = Workbooks("GuidelinesMapping_oldtonew.xls").Sheets("Sheet1")

intCx = s1.Rows(1).Find("Document Name or Description").Column
intCx = s2.Rows(1).Find("Old Number").Column


If srchRng Is Nothing Then
MsgBox "Data not found"
Exit Function
Else




End Function

 

[scpankow]

Skip,
Yes I have tried Edit > Replace on the sheet. The trouble is that I have over 100 of these to find/replace and I dont want to do them all by hand. The macro recorder is giving me alot of junk code and it is confusing to me.

Shannon

 

[SkipVought]

Just record doing replace ONE TIME.

Post your code.

We can show you how to do, "... over 100 of these..." in a loop.

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[AnotherHiggins]

If you want to learn VBA, more power to you.

But I really think you're making it overly and needlessly complex. You should store data in discrete chunks.

Well, they kind of go together. For example:

IM-GEN-G0004 - Local and Wide Area Network Security

So? That is no reason to lump disparate data together in a single field.

First Name and Last Name "kind of go together", too. But how many online forms have you filled out where they are entered into the same field? None? What's that tell you?

[!]Different data type = different field[/!]. It's that simple.

Why?

Well, consider the convolutions you're going through right now.

If the description was in it's own field, you could use your list of updates to do a simple Lookup. It would take about no minutes. Seriously.

You could even do it via VBA if you really wanted to.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[scpankow]

John,
I totally get what you are saying, however, the problem is that I did not create the worksheets and this is how they have to stay. Do you see my problem? Any suggestions would be welcome...

Shannon

 

[scpankow]

Skip,
Ok, here is the macro I recorded...

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 5/14/2008 by scpankow
'

'
Cells.Select
Range("B1").Activate
Cells.Replace What:="IM-SEC-G0017", Replacement:="IM-G0057", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

How do we do the loop?

Thanks,
Shannon

 

[Bong]

Set srchRng = Range("s1.columns(intCx)")
Set srchValue = Range("s2.columns(intCx)")

This is the wrong syntax, and not useful, anyway.

Set s1 = Workbooks("HIPAA One Page Wonder_05012006.xls").Sheets("IM")
Set s2 = Workbooks("GuidelinesMapping_oldtonew.xls").Sheets("Sheet1")

intCx = s1.Rows(1).Find("Document Name or Description").Column
intC[red]y[/red] = s2.Rows(1).Find("Old Number").Column

This establishes the columns of the ranges of interest (note the correction in [red]red[/red], you don't want to overwrite one value with another)

Now without seeing the data, I can't really be sure what might be an efficient way to proceed, but brute force always works. So let's say that for every value in the column "Old Number", you're going to look in every value in the column "Document Name or Description" and see if the value in the first case is somewhere in the value in the second:

for each c1 in s2.columns(intCy).cells
  for each c2 in s1.columns(intCx).cells
    intSx = instr(1,c2.value,c1.value)
    if (intSx>0) then
         <do your thing>
    end if
  next
next

_________________
Bob Rashkin

 

[SkipVought]

Assuming that your OldGuidelinesList has a NewGuidline in the adjacent cell to the right, define the OldGuidelinesList range PROPERLY, as noted below

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 5/14/2008 by scpankow
'
    Dim r As Range
'
    For Each r In Sheets([b]sheetContainingYourlists[/b]).Range([b]YourOldGuidlinesList[/b])
        sheets([b]sheetContainingYourDATA[/b]).Cells.Replace What:=r.Value, Replacement:=r.Offset(0, 1).Value, LookAt:= _
            xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    Next
End Sub

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[scpankow]

Bob,
Ok, I have edited it:

Sub replaceGuidelines()

Set s1 = Workbooks("HIPAA One Page Wonder_05012006.xls").Sheets("IM")
Set s2 = Workbooks("GuidelinesMapping_oldtonew.xls").Sheets("Sheet1")

intCx = s1.Rows(1).Find("Document Name or Description").Column
intCy = s2.Rows(1).Find("Old Number").Column

For Each c1 In s2.Columns(intCy).Cells
For Each c2 In s1.Columns(intCx).Cells
intSx = InStr(1, c2.Value, c1.Value)
If (intSx > 0) Then
' <do your thing>
End If
Next
Next

End Sub


What is the <do your thing>?
also, when I run it, I get a subscript out of range error 9 on this line:

Set s2 = Workbooks("GuidelinesMapping_oldtonew.xls").Sheets("Sheet1")

The sheets are in two separate workbooks, could that be the problem?

Shannon