Find and Replace Excel Data based on cell data

[kwbMitel]

Newbie to Visual Basic - Sorry

Mainly having issues figuring out what commands do what I want them to. Help files not cutting it for me (too many choices).

I'm tring to write a Macro to read cell data in one file and use the data to find and replace data in another.

The data in the source file used for comparison is always changing so I don't want to put the data directly into the Macro.

Basic concept:

Read data from source (Column "A" and "B") and store in 2 dimensional Array - Can't figure this. Array not specifically required as long as I can reference the variables in "Pairs"

Prompt user to select sheet to edit - Got this part
Prompt user to select range to replace - got this part

Initiate Find and replace - think I've got this part using Case statement and cell.value referenced to variables in the array.

I'm good at reading help files if someone can point me to the correct commands for the first part.



*******************************************************
Occam's Razor - All things being equal, the simplest solution is the right one.

 

[SkipVought]

Hi,

"Read data from source (Column "A" and "B") and store in 2 dimensional Array - Can't figure this. Array not specifically required as long as I can reference the variables in "Pairs"

I'd use MS Query. No VBA at all.

Skip,

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

 

[SkipVought]

FYI, faq68-5829

And Help has some good info too.

Skip,

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

 

[kwbMitel]

MS Query looks interesting for looking up the data but I don't see how I can relate this to the remaining task of then using that data to find and replace in another document.

I'm having trouble enough getting my head around VBA, I don't need more unknowns.

I've programmed successfully with Basic, and Pascal and I've been able to use that knowledge to figure out "C" but I just can't find the commands I need.

With VBA, how do I read the data in a cell and store it as a variable for reference later?


*******************************************************
Occam's Razor - All things being equal, the simplest solution is the right one.

 

[SkipVought]

Turn on your macro recorder and Edit > FIND & Replace the string of interest.

Skip,

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

 

[kwbMitel]

How would you use a macro recorder to do an if then statement?

If data = x then replace data with y

Your answers may be obvious to you but I'm sorry, I'm not understanding.



*******************************************************
Occam's Razor - All things being equal, the simplest solution is the right one.

 

[kwbMitel]

Besides, I'm fairly confident I have the find and replace sequence down.

What I'm missing is the ability to place existing paired data into matched variables for the purpose of finding and replacing.



*******************************************************
Occam's Razor - All things being equal, the simplest solution is the right one.

 

[Gavona]

How would you use a macro recorder to do an if then statement?

You don't as there is native functionality to do that. Record yourself using Edit,Replace

To get the values to search for and replace with:

For Each myCell in Range(A1:A8)
 MySearch = myCell.value
 myReplace = myCell.offset(0,1).value
 {do the replace using the above variables}
Next myCell

Gavin

 

[SkipVought]

Well what you are asking is so vague. Please post your code and explain where you need help.

Skip,

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

 

[kwbMitel]

Sorry if I'm frustrating you guys.

I can't post code as I am looking for a command that I don't know so... no code to show.

I'll try to be more specific in what I am trying to do.

I have a List of 1000 schools listed by name and a short code.

The reports I get from my database only list the short code.

I have a cross reference file and I can find and replace 1 at a time no problem.

Gavona's post is a good example of finding and replacing all of 1 short code with 1 school name. I can do this already.

I want be able to convert my cross reference file into variables that I can then use in a looping find and replace 1 at a time until all are found and replaced.

The school list is constantly being updated by numerous parties so I don't want to place the X-ref in the macro itself.

I am looking for the command to read a cell and populate a variable.



*******************************************************
Occam's Razor - All things being equal, the simplest solution is the right one.

 

[SkipVought]

The why would you use a REPLACE? are the School Names CHANGING over time for a given CODE?

It seems that all you really need is a LOOKUP function on the spreadsheet: Lookup the CODE and return the School or vis versa. This is Excel 101.

Skip,

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

 

[SkipVought]

In fact it you use MS query to get the data from your database, AND you have the lookup formula in an adjacent column, and it can ALWAYS, AUTOMATICALLY adjust to the resultset data range or rows. NO VBA REQUIRED!

Skip,

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

 

[ettienne]

I agree, VLOOKUP or HLOOKUP should do that for you.

 

[kwbMitel]

Ok,

I didn't want to let go of all the time I've already invested trying to figure this out.

I'm completely self taught so there's serious gaps in my skill set.

I'll see what I can find on MS Query.

Thanks for the assistance so far.




*******************************************************
Occam's Razor - All things being equal, the simplest solution is the right one.

 

[Gavona]

Gavona's post is a good example of finding and replacing all of 1 short code with 1 school name.

It is more than that. If Range(A1:A8) was replaced with something that refered to the range containing the list of 1000 schools then it would step through each entry in the list getting the shortcode (myCell.value) and the name (myCell.offset(0,1).value) assuming that the text is in the column to the right of the code. Then before moving on to the next entry on your list you would insert the code to relace short code with name for that entry.



If using vlookup instead remember to ensure that the source file is open, then copy the formula, then copy and paste it to values. This will keep down file size and recalculation time.

This is the generic code that I use for this sort of task. The calling routine would handle identifying the "TitleCell", opening the linked workbook and Switching screen updating off and on.

Sub FormulaCopy3(MyTitleCell As Range)
'This sub is used by the FormulaCopy macro _
It copies formulae from two rows above the "TitleCell" passed to it from the calling macro _
to all cells below the TitleCell to the bottom of the "CurrentRegion" _
Ensure your database follows best design practice - separated from anything else _
on the sheet by blank rows and columns.

Dim MyRange As Range
Set MyRange = Range(MyTitleCell.Offset(1, 0), MyTitleCell.Offset(MyTitleCell.CurrentRegion.Rows.Count - 1, 0))
MyTitleCell.Offset(-2, 0).Copy Destination:=MyRange
If Application.Calculation = xlCalculationManual Then MyRange.Calculate
MyRange.Copy
MyRange.PasteSpecial _
    Paste:=xlPasteValues, _
    Operation:=xlNone, _
    SkipBlanks:=False, _
    Transpose:=False
Application.CutCopyMode = False
End Sub

To open ALL linked workbooks you could take a look at thread707-1215569

Gavin