Basic VBA IF statement

[Jasperverdam]

Hi All,

I have a very basic vba IF statement problem.

The situation is that I have a bunch of sheets in excel, every sheet = 1 client
I want to create a search engine in an overview sheet, so you just have type the name of the client, press a vba button and it automatically goes to that sheet.
The name of the client can only be found on its personal client page, so nowhere else.

What i've done first is create a list with regular IF statements (non-vba), and the outcome is either "Goto" (the typed client name matches the client on sheet 1) or "Dontgo":

Column N Column O
Sheet 1: "Goto"
Sheet 2: "Dontgo"
Sheet 3: "Dontgo"
Sheet 4: "Dontgo"
etc.etc.

Now I have the following vba but it always stops at sheet 1 cell D2:F2, regardless of cell "O3" having "Goto" or "Dontgo".

Sub Search()

If Range("O3").Value = "Goto" Then
Application.Goto Sheets("1").Range("D2:F2"), True
ElseIf Range("O4").Value = "Goto" Then
Application.Goto Sheets("2").Range("D2:F2"), True
ElseIf Range("O5").Value = "Goto" Then
Application.Goto Sheets("3").Range("D2:F2"), True
ElseIf Range("O6").Value = "Goto" Then
Application.Goto Sheets("4").Range("D2:F2"), True

Ive tried making the value in "O3" a hard value, no difference.
Try Range.Select, no difference.

Any help is appreciated!

Jasper

[Alf]

In your case since you have a sheet for each customer I would set sheet name = customer name and use a macro like this.

Please Login or Register  to view this content.

Alf

[jmac1947]

Hello Jasper, and welcome to the forums

One of the important things you will see as you progress is that it always an excellent idea to include in your request for assistance a sample workbook that shows exactly what you have and what you are expecting. Most of the contributors who provide suggestions / help are people with "regular day jobs" and are here as volunteers.

It is sadly true that many people will simply pass a thread by if they have to spend time trying to build what they imagine you already have

A bonus on top of getting people interested is that you often get some pretty good code returned that you can use to complete the resolution of your problem.

I should also say, take a few minutes to read the forum rules (particularly in respect to posting code) to avoid a gentle rebuke from the moderators / senior experts. Unfortunately I don't have the ability to wrap your code so please do that as soon as you can.

Now to your problem.

Is it an option to have some form of extra worksheet (lets call it a "client register") as your first sheet in the workbook that contains a list of the customer names where the individual row is hyperlinked to the actual page??

Just a thought that may be worth exploring. This can actually be set up manually (depending on the number of sheets OR how the customer sheets get created) pretty easily without the need to get into VBA at all