PDA

View Full Version : Sleeper: A few questions



IVY440
08-10-2005, 06:30 AM
I'm still busy with this project and I still have a few questions
1. To give a variable the value of a string in A1, is it enough to encode it like this?

variable1 = Range("A1")

2. I want to see if this value is also on Sheet2 in the first column.
I tried with

Variable2 = IsError(WorksheetFunction.VLookup(Variable1, _
Sheets("Sheet2").Range("A:K"), 2, False) = True)
If Variable2 = True Then ...

But it gives an error with the worksheetfunction.vlookup

3. If I looked up the value that belongs to Variable1 (let's say that's Variable3 ), how can I check if the sheet "Variable3 - Variable2" already exists?


Thanx for the help you can give me :clap: :bow:

jindon
08-10-2005, 06:36 AM
Hi

As I mentioned earlier, if you change Worksheetfunction to Application, it returns error value as a result, if not the line becomes error, so you need On Error Resume Next to avoid debugging.

try to change Worksheetfunction to Application

IVY440
08-10-2005, 06:42 AM
That's right. But that leaves 2 more problems. ;)

jindon
08-10-2005, 06:46 AM
1) better



variable1 = Range("A1") .Value

3) Isn't it

If variable1=variable2 Then

Is this what you mean?

jindon
08-10-2005, 06:47 AM
3. If I looked up the value that belongs to Variable1 (let's say that's Variable3 ), how can I check if the sheet "Variable3 - Variable2" already exists?
Sorry, I don't quite understand the meaning

IVY440
08-10-2005, 06:51 AM
I'll try to explain the situation for 3.
I put a city in A1.
I have a sheet with the countries in which the cities are.
So the Application.Vlookup part looks up the country belonging to the city.
The sheets aren named "Country - City"
Now I want to check if the "Country - City" sheet of the City (in A1) exists or not.

jindon
08-10-2005, 07:00 AM
Well, if I understand correctly, you don't need to lookup the table


Sub test()
Dim x, msg As String
On Error Resume Next
x = Len(Sheets(Range("a1").Value).Name)
If x > 0 Then
msg = " exists"
Else
msg = " not found"
End If
MsgBox "Sheet named " & Range("a1").Value & msg
End Sub

IVY440
08-10-2005, 07:07 AM
I don't really understand what your code does.
But I think the best is to lookup the country and then check the sheets.

jindon
08-10-2005, 07:11 AM
The code will check if the sheet named same as Range("a1").value exists or not.

line should read as
x = Len(Sheets("Country - " & Range("a1").Value).Name)

Am I misunderstanding?

IVY440
08-10-2005, 07:16 AM
No, the country is also in the sheet's name. So the country's name should also be incorporated.

jindon
08-10-2005, 07:21 AM
I put a city in A1.
I have a sheet with the countries in which the cities are.
So the Application.Vlookup part looks up the country belonging to the city.
The sheets aren named "Country - City"
Now I want to check if the "Country - City" sheet of the City (in A1) exists or not.

Say, if Range("a1")= "London"
Do you want to check if sheet named "Country - London" exists or not?

IVY440
08-10-2005, 07:23 AM
Yes with the Country the looked up value from sheet2.

jindon
08-10-2005, 07:34 AM
still not sure if this is what you want


Sub test()
Dim x, variable1, variable2, msg As String
variable1 = Range("a1").Value
variable2 = Application.VLookup(variable1, Sheets("Sheet2").Range("A:K"), 2, False)
If IsError(vaiable2) Then
MsgBox "City of " & variable1 & " not found": Exit Sub
Else

On Error Resume Next
x = Len(Sheets(variable2 & " - " & Range("a1").Value).Name)
If x > 0 Then
msg = " exists"
Else
msg = " not found"
End If
MsgBox "Sheet named " & Range("a1").Value & msg

End If
End Sub

IVY440
08-10-2005, 07:37 AM
I begin to get the point now, but what is this code doing:


x = Len(Sheets(variable2 & " - " & Range("a1").Value).Name)

jindon
08-10-2005, 07:43 AM
OK

If lookup get the value like England for London, variable2 should be England

Test if Length of name of the sheet that is England - London is greater than 0,

if it doesn't exists, the line will be error, but On Error Resume Next will carry on the code to the next line and obviously it will go to Else part of the If statement.

does it explain?

IVY440
08-10-2005, 07:50 AM
Is there no explicit function to look if a sheet with a name already exists?

jindon
08-10-2005, 08:00 AM
I don't think so.
but you can make it as a function



Sub test()
Dim x, variable1, variable2, msg As String
variable1 = Range("a1").Value
variable2 = Application.VLookup(variable1, Sheets("Sheet2").Range("A:K"), 2, False)
If testsheet(variavle1 & " - " & variable2) Then
msg = " found"
Else
msg = " not found"
End If
MsgBox "Sheet named: " & variable1 & " - " & variable2 & msg
End Sub

Function testsheet(sn As String) As Boolean
On Error Resume Next
x = Len(Sheets(sn).Name)
If x > 0 Then
testsheet = True
Else
testsheet = False
End If
End Function

IVY440
08-10-2005, 08:16 AM
Thanx Jindon, I will try this.
But because if it's a part of a bigger project I can't tell immediately if this is working. I'll keep the thread Unsolved until I know for sure it works.

jindon
08-10-2005, 08:18 AM
Sure, test it hard.

I usually use it before adding new sheets to test if the name already exists or not for myself.

Bob Phillips
08-10-2005, 08:53 AM
This is what I use




Sub TestSheetExists()
If Not SheetExists("Sheet99") Then
Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = "Sheet99"
End If
End Sub

Function SheetExists(Sh As String, _
Optional wb As Workbook) As Boolean
Dim oWs As Worksheet
If wb Is Nothing Then Set wb = ActiveWorkbook
On Error Resume Next
SheetExists = CBool(Not wb.Worksheets(Sh) Is Nothing)
On Error GoTo 0
End Function

IVY440
08-11-2005, 02:47 AM
Jindon, it doesn't want to check the name of the sheet so there must be a mistake somewhere. I also tried to combine the 2 strings into 1 but also that's not working.

jindon
08-11-2005, 03:08 PM
Jindon, it doesn't want to check the name of the sheet so there must be a mistake somewhere. I also tried to combine the 2 strings into 1 but also that's not working.

Check what is happening on your code.

While you are in VB editor, go to View -> Local Window then click somewhere on the sub in question.

As you hit F8, it executes the sub line by line and you can see the movements of all the variables in the Local Window.

check variable1 and variable2, if they get the correct value or not.

geekgirlau
08-11-2005, 09:56 PM
I usually go the low-tech option ... :Drooling:



Function SheetFound(strSheet As String) As Boolean
On Error Resume Next
Debug.Print Sheets(strSheet).Name
SheetFound = Err.Number = 0
End Function