Making Excel do what I need

[mikhailia]

Ok I need excel to do one of two things, to accomplish my goal.



I either need to make it do this:
If column A in worksheet one corresponds with Column E in worksheet two, withing the same workbook, I need D,E,F,G of worksheet one to paste into D,E,F,G of worksheet two... Is there anyway to do that... I am new to excel sort of... At least this part...


Or...

I need it so I can use the find tool and find/replace multiple items at once but replace them all with the same thing. What would I use in the find box to seperate different things for it to find... I tried commas and colons...

[Bryan Hessey]

Ok I need excel to do one of two things, to accomplish my goal.



I either need to make it do this:
If column A in worksheet one corresponds with Column E in worksheet two, withing the same workbook, I need D,E,F,G of worksheet one to paste into D,E,F,G of worksheet two... Is there anyway to do that... I am new to excel sort of... At least this part...


Or...

I need it so I can use the find tool and find/replace multiple items at once but replace them all with the same thing. What would I use in the find box to seperate different things for it to find... I tried commas and colons...

Hi,

the Find/Replace is single item, but you could loop the Replace with some VBA code.

to extract thecolumns D E F & G according to a match on column A, in D1 of the second sheet, put

=VLookup(A1,Sheet1!A:G,4,False)

and for E F & G put

=VLookup(A1,Sheet1!A:G,5,False)
=VLookup(A1,Sheet1!A:G,6,False)
=VLookup(A1,Sheet1!A:G,7,False)

and formula fill those down the 4 columns as far as data in the A column of Sheet2

Formula fill is described at http://www.mvps.org/dmcritchie/excel/fillhand.htm

After that you can 'freeze' that data if you need to by Select the 4 columns, anmd Copy, then Paste Special = Values back over themselves, this removes the formula and keeps the shown Value.

hth
---

[MSP77079]

I am afraid you will not be happy with this answer.

You can do either of the two things you want. But, either one will require some VBA programming (a macro). The copy and paste thing is pretty simple to do in VBA. But, it is impossible to do with a Formula.

The Excel Find/Replace is very powerful in Excel 2003 (not sure which version you are using); it can search an entire workbook and display all occurances at once. But, it cannot look for multiple things at once. It does not accept SQL nor include any other type of query feature. So, to do this would require something like a form, or multiple input boxes to gather each of your search strings separately, then execute these searches individually and bring the results together.

From what you have described, if I were asked to do this, I would go with option 1. It sounds a lot easier to implement than option 2.

The only thing I would need to understand a bit more is what you mean by

"If column A in worksheet one corresponds with Column E in worksheet two"

Is this a row-by-row comparison? So, you copy stuff from Sheet1 to Sheet2 or vice versa based on one row's data at a time? Or is it an entire column comparison, where you move the entire columns D,E,F, and G?

[mikhailia]

Yes, I mean going row by row. I am sorry.

I've never done any of this and I am sort of overwhelmed. I'm trying to copy and paste some 9000 things by hand... one... at... a... time...

Yeah it is row by row

[MSP77079]

OK, this should work. (Be sure to save a back-up copy before you test it.)

Please Login or Register  to view this content.

[mikhailia]

Thank you very much... I hate asking but... Where do I paste this? I have never done these before.

[MSP77079]

Now, I am just guessing, but the next logical question is "what do I do with this?!?"

OK, I would start with only the one workbook open to be sure you make no mistakes. Then open the Visual Basic Editor; you can use Tools >> Macro >> Visual Basic Editor, or use Alt+F11).

On the VB Editor's menu select Insert >> Module. Copy the code from this forum and paste it there.

If your worksheets are not named "Sheet1" and "Sheet2", you will need to change this.


If you do not want to start the searching in row 1, then change this line:

Please Login or Register  to view this content.

and replace the "1" with the row you want to start in. If you do not want to continue to the last row, then replace "lastRow" with the row where you want to stop the search/replace.

Then, go back to the normal Excel view. Press Alt+F8, then press the Run button.

[MSP77079]

I did not finish typing fast enough! But I did correctly guessed the next question.

[mikhailia]

Thank you again. I came across a problem though, It gave me an error saying "Could not execute code in breakmode."

Yes You are very good at mindreading. Thank you.

[MSP77079]

Well, that's a very interesting message.

When you get that message are you looking at the normal Excel window or the VB Editor? If the VB Editor, is any line of code highlighted? If yes, is it highlighted yellow or brown?

[mikhailia]

I fixed that using what little bit of brain I have that works... But got a new error
"Runtime Error 9: Script out of range."


It gives me some options:
End
Or
Debug

Edit:

I hit debug and it highlighted yellow the part "Set ws2 = Sheets("Sheet2")" Was I supposed to put my sheet names in there?

[MSP77079]

Hit Debug and tell me which line is highlighted in yellow.

[mikhailia]

Set ws2 = Sheets("Sheet2")

That is where I put ym sheet name right?


Well The sheets are labled SBK and Sheet 1... AS I named them but to the side it says

Sheet1(SBK)
Sheet2(Sheet1)

In a seperate little panel
So I just left them...

[MSP77079]

OK, great!

We will fix that, and meanwhile I see another problem ... this one I created.

So, just to be absolutely certain I understand ... you want to look at the entry in column A of the sheet that has on its Tab "SBK", and compare that with column E of the sheet that has on its Tab "Sheet1". Is that correct?

[mikhailia]

Yeah... IF A on SBK matches E on Sheet1. Then I want columns DEFG of the same row as SKB!A to paste into columns DEFG of Sheet1 again ont hes ame row as the e that matched

[MSP77079]

OK, here is updated code.

Please Login or Register  to view this content.

I will explain this more in another note while you try this.

[MSP77079]

to the side it says

Sheet1(SBK)
Sheet2(Sheet1)

Each worksheet has 2 names, if you will. The first one (not in parentheses) is called the "CodeName"; you never see this one in Excel; you only see it in the VB Editor. The second one (in parentheses) is simply the "Name".

As this is your first encounter with VBA (and you are doing quite well, I must say), you should not even think about the CodeName.

The run-time error

"Runtime Error 9: Script out of range."

was caused because we told Excel we wanted to use "Sheet2" (a Name) that does not exist. So, this should solve that problem.

The other problem I referred to was my mistake. I was comparing Column A with Column A instead of comparing Column A with Column E.

Hope all is working now.

[mikhailia]

You've done so much already I hate to say this... I got the breakmost problem but this time something was highlighted...

Sub findAndReplace()

It was in yellow.Everything else stayed the same.

I might have as seizure... I haven't done excel since fifth grade... and it was primitive back them. And we only did graphs for fun. hahahaha. I am in 11th now... So it's been a long time ><

[MSP77079]

If it is only highlighted in Yellow, then find the "Reset" button (it is a small blue square that looks like the "Stop" button on a VCR or DVD player) and press it. Then, return to Excel and try again.

I suspect the problem is that you are pressing F8 while still in VB Editor. You need to go back to the normal Excel view before pressing F8; and it should be Alt+F8, not just F8.

If it is also brown, then the problem is that you put a breakpoint in the program unintentionally. If this is the case, then go to the left side of the brown line and click on the brown circle to remove the breakpoint.

11th grade. Wow. Keep going at this rate and you will be a wiz in Excel before you know it.

[mikhailia]

I tried again and it is... well doing something... It has the hourglass... I think it worked. Thank you so much. If it doesn't. Well I will say something. You've just saved me four days labour. It worked when I put the spreadsheet to the SBK one.

Edit... Excel froze... I have to try again but this time I won't be replying for a while...

Edit again!

It unfroze!!!! And most the work is done!!! I jsut gotta spot check it and I think I am good. Thank you. I am forever endebted to you. Unfortunately I am no good at this. So never ask me for excel advise.