Macro syntax difficulties (long post)
 

The suspect the source of my problem is I am running Excel 2003 and my
reference books are for Excel 2000 and Excel/VB for Windows 95. Apparently,
different versions use different syntax. Or maybe I have Dain-Bramage or
something€¦.

SETUP: A user selects a date, enters data and exits the program.

The data entered is saved on a page named RawDailyData. The first ROW has a
date for a certain number of days entered on it, beginning in cell C1

Page 1 is named StartPage, Page 2 is named DataInput, Page 3 is named
RawDailyData

When a user opens the file and enables the macros, the file opens to the
StartPage. User sees the following:

Step 1 = Choose a date €œA date is selected from a listbox in a cell named
EntryDate€�
Step 2 = Click the GO button to enter data for selected date
Step 3 = Click the Save & Exit button

The GO command button calls the GO macro. I want the GO macro to do the
following:
1. See if data already exists on the RawDailyData page for the selected
EntryDate.
2. If true, bring up the UserForm1

€¢ UserForm1 has a message and a single command button, named RETURN. The
message says to click the return button and select another EntryDate, because
data for that date has already been entered. Currently, the button simply
unloads the userform, essentially returning the user to the EntryDate select
box. (I do not want the user to be able to overwrite existing data)

3. If false, (that is, no previous data has been entered for the selected
EntryDate)
nothing happens when the GO button is clicked except that the user is
directed
to the first data entry cell on the DataInput page to begin entering
data.

I can achieve a TRUE/FALSE result, using a nested worksheet function
IF/HLOOKUP.
=IF(HLOOKUP(EntryDate,RawDailyData!C1:IM2,2,FALSE) <"",TRUE,FALSE)

The first line of data is saved on Row 2. If anything, even a zero, is
entered in that cell, the formula results in a TRUE. The cell must be blank
for a FALSE.

PROBLEM: When I try to achieve the same result using a macro, I simply cannot
get all the syntax correct. Any assistance would be greatly appreciated.

Randy

Macro syntax difficulties (long post)
 

Hi,
Try this:

Dim res As Variant, EntryDate, LookInRange As Range

Set LookInRange = Worksheets("RawDailyData").Range("C1:IM2")

res = Application.HLookup(EntryDate, LookInRange, 2, False)

If IsError(res) Then ' No valid Entry Date
MsgBox "Not Found"
Else
If res < "" Then
MsgBox "true"
Else
MsgBox "False"
End If
End If

HTH

"RAP" wrote:

The suspect the source of my problem is I am running Excel 2003 and my
reference books are for Excel 2000 and Excel/VB for Windows 95. Apparently,
different versions use different syntax. Or maybe I have Dain-Bramage or
something€¦.

SETUP: A user selects a date, enters data and exits the program.

The data entered is saved on a page named RawDailyData. The first ROW has a
date for a certain number of days entered on it, beginning in cell C1

Page 1 is named StartPage, Page 2 is named DataInput, Page 3 is named
RawDailyData

When a user opens the file and enables the macros, the file opens to the
StartPage. User sees the following:

Step 1 = Choose a date €œA date is selected from a listbox in a cell named
EntryDate€�
Step 2 = Click the GO button to enter data for selected date
Step 3 = Click the Save & Exit button

The GO command button calls the GO macro. I want the GO macro to do the
following:
1. See if data already exists on the RawDailyData page for the selected
EntryDate.
2. If true, bring up the UserForm1

€¢ UserForm1 has a message and a single command button, named RETURN. The
message says to click the return button and select another EntryDate, because
data for that date has already been entered. Currently, the button simply
unloads the userform, essentially returning the user to the EntryDate select
box. (I do not want the user to be able to overwrite existing data)

3. If false, (that is, no previous data has been entered for the selected
EntryDate)
nothing happens when the GO button is clicked except that the user is
directed
to the first data entry cell on the DataInput page to begin entering
data.

I can achieve a TRUE/FALSE result, using a nested worksheet function
IF/HLOOKUP.
=IF(HLOOKUP(EntryDate,RawDailyData!C1:IM2,2,FALSE) <"",TRUE,FALSE)

The first line of data is saved on Row 2. If anything, even a zero, is
entered in that cell, the formula results in a TRUE. The cell must be blank
for a FALSE.

PROBLEM: When I try to achieve the same result using a macro, I simply cannot
get all the syntax correct. Any assistance would be greatly appreciated.

Randy

Macro syntax difficulties (long post)
 

Toppers,

Thanks for the reply. I works, but not "all the way."
No matter if existing data is there or not, all I get is the "Not Found"
statement.

It acts as if the macro is not getting to the ELSE statement. Any ideas?

Thanks again,
Randy

"Toppers" wrote:

Hi,
Try this:

Dim res As Variant, EntryDate, LookInRange As Range

Set LookInRange = Worksheets("RawDailyData").Range("C1:IM2")

res = Application.HLookup(EntryDate, LookInRange, 2, False)

If IsError(res) Then ' No valid Entry Date
MsgBox "Not Found"
Else
If res < "" Then
MsgBox "true"
Else
MsgBox "False"
End If
End If

HTH

"RAP" wrote:

The suspect the source of my problem is I am running Excel 2003 and my
reference books are for Excel 2000 and Excel/VB for Windows 95. Apparently,
different versions use different syntax. Or maybe I have Dain-Bramage or
something€¦.

SETUP: A user selects a date, enters data and exits the program.

The data entered is saved on a page named RawDailyData. The first ROW has a
date for a certain number of days entered on it, beginning in cell C1

Page 1 is named StartPage, Page 2 is named DataInput, Page 3 is named
RawDailyData

When a user opens the file and enables the macros, the file opens to the
StartPage. User sees the following:

Step 1 = Choose a date €œA date is selected from a listbox in a cell named
EntryDate€�
Step 2 = Click the GO button to enter data for selected date
Step 3 = Click the Save & Exit button

The GO command button calls the GO macro. I want the GO macro to do the
following:
1. See if data already exists on the RawDailyData page for the selected
EntryDate.
2. If true, bring up the UserForm1

€¢ UserForm1 has a message and a single command button, named RETURN. The
message says to click the return button and select another EntryDate, because
data for that date has already been entered. Currently, the button simply
unloads the userform, essentially returning the user to the EntryDate select
box. (I do not want the user to be able to overwrite existing data)

3. If false, (that is, no previous data has been entered for the selected
EntryDate)
nothing happens when the GO button is clicked except that the user is
directed
to the first data entry cell on the DataInput page to begin entering
data.

I can achieve a TRUE/FALSE result, using a nested worksheet function
IF/HLOOKUP.
=IF(HLOOKUP(EntryDate,RawDailyData!C1:IM2,2,FALSE) <"",TRUE,FALSE)

The first line of data is saved on Row 2. If anything, even a zero, is
entered in that cell, the formula results in a TRUE. The cell must be blank
for a FALSE.

PROBLEM: When I try to achieve the same result using a macro, I simply cannot
get all the syntax correct. Any assistance would be greatly appreciated.

Randy

Macro syntax difficulties (long post)
 

The code below doesn't provide an entrydate...or do you supply that as a
parameter for the macro?
Which dateformat do you use to supply the entrydate?

Lee-Z


"RAP" wrote in message
...
Toppers,

Thanks for the reply. I works, but not "all the way."
No matter if existing data is there or not, all I get is the "Not Found"
statement.

It acts as if the macro is not getting to the ELSE statement. Any ideas?

Thanks again,
Randy

"Toppers" wrote:

Hi,
Try this:

Dim res As Variant, EntryDate, LookInRange As Range

Set LookInRange = Worksheets("RawDailyData").Range("C1:IM2")

res = Application.HLookup(EntryDate, LookInRange, 2, False)

If IsError(res) Then ' No valid Entry Date
MsgBox "Not Found"
Else
If res < "" Then
MsgBox "true"
Else
MsgBox "False"
End If
End If

HTH

"RAP" wrote:

The suspect the source of my problem is I am running Excel 2003 and my
reference books are for Excel 2000 and Excel/VB for Windows 95.
Apparently,
different versions use different syntax. Or maybe I have Dain-Bramage
or
something..

SETUP: A user selects a date, enters data and exits the program.

The data entered is saved on a page named RawDailyData. The first ROW
has a
date for a certain number of days entered on it, beginning in cell C1

Page 1 is named StartPage, Page 2 is named DataInput, Page 3 is named
RawDailyData

When a user opens the file and enables the macros, the file opens to
the
StartPage. User sees the following:

Step 1 = Choose a date "A date is selected from a listbox in a cell
named
EntryDate"
Step 2 = Click the GO button to enter data for selected date
Step 3 = Click the Save & Exit button

The GO command button calls the GO macro. I want the GO macro to do
the
following:
1. See if data already exists on the RawDailyData page for the
selected
EntryDate.
2. If true, bring up the UserForm1

. UserForm1 has a message and a single command button, named RETURN.
The
message says to click the return button and select another EntryDate,
because
data for that date has already been entered. Currently, the button
simply
unloads the userform, essentially returning the user to the EntryDate
select
box. (I do not want the user to be able to overwrite existing data)

3. If false, (that is, no previous data has been entered for the
selected
EntryDate)
nothing happens when the GO button is clicked except that the user
is
directed
to the first data entry cell on the DataInput page to begin
entering
data.

I can achieve a TRUE/FALSE result, using a nested worksheet function
IF/HLOOKUP.
=IF(HLOOKUP(EntryDate,RawDailyData!C1:IM2,2,FALSE) <"",TRUE,FALSE)

The first line of data is saved on Row 2. If anything, even a zero, is
entered in that cell, the formula results in a TRUE. The cell must be
blank
for a FALSE.

PROBLEM: When I try to achieve the same result using a macro, I simply
cannot
get all the syntax correct. Any assistance would be greatly
appreciated.

Randy

Macro syntax difficulties (long post)
 

Lee-Z,
The "EntryDate" in the following expression refers to a named cell that has
the date entered into it. The formatting for the EntryDate, as well as the
dates on the "RawDailyData" page is the same.... 5/1/2005.

Randy

res = Application.HLookup(EntryDate, LookInRange, 2, False)



"Lee-Z" wrote:

The code below doesn't provide an entrydate...or do you supply that as a
parameter for the macro?
Which dateformat do you use to supply the entrydate?

Lee-Z


"RAP" wrote in message
...
Toppers,

Thanks for the reply. I works, but not "all the way."
No matter if existing data is there or not, all I get is the "Not Found"
statement.

It acts as if the macro is not getting to the ELSE statement. Any ideas?

Thanks again,
Randy

"Toppers" wrote:

Hi,
Try this:

Dim res As Variant, EntryDate, LookInRange As Range

Set LookInRange = Worksheets("RawDailyData").Range("C1:IM2")

res = Application.HLookup(EntryDate, LookInRange, 2, False)

If IsError(res) Then ' No valid Entry Date
MsgBox "Not Found"
Else
If res < "" Then
MsgBox "true"
Else
MsgBox "False"
End If
End If

HTH

"RAP" wrote:

The suspect the source of my problem is I am running Excel 2003 and my
reference books are for Excel 2000 and Excel/VB for Windows 95.
Apparently,
different versions use different syntax. Or maybe I have Dain-Bramage
or
something..

SETUP: A user selects a date, enters data and exits the program.

The data entered is saved on a page named RawDailyData. The first ROW
has a
date for a certain number of days entered on it, beginning in cell C1

Page 1 is named StartPage, Page 2 is named DataInput, Page 3 is named
RawDailyData

When a user opens the file and enables the macros, the file opens to
the
StartPage. User sees the following:

Step 1 = Choose a date "A date is selected from a listbox in a cell
named
EntryDate"
Step 2 = Click the GO button to enter data for selected date
Step 3 = Click the Save & Exit button

The GO command button calls the GO macro. I want the GO macro to do
the
following:
1. See if data already exists on the RawDailyData page for the
selected
EntryDate.
2. If true, bring up the UserForm1

. UserForm1 has a message and a single command button, named RETURN.
The
message says to click the return button and select another EntryDate,
because
data for that date has already been entered. Currently, the button
simply
unloads the userform, essentially returning the user to the EntryDate
select
box. (I do not want the user to be able to overwrite existing data)

3. If false, (that is, no previous data has been entered for the
selected
EntryDate)
nothing happens when the GO button is clicked except that the user
is
directed
to the first data entry cell on the DataInput page to begin
entering
data.

I can achieve a TRUE/FALSE result, using a nested worksheet function
IF/HLOOKUP.
=IF(HLOOKUP(EntryDate,RawDailyData!C1:IM2,2,FALSE) <"",TRUE,FALSE)

The first line of data is saved on Row 2. If anything, even a zero, is
entered in that cell, the formula results in a TRUE. The cell must be
blank
for a FALSE.

PROBLEM: When I try to achieve the same result using a macro, I simply
cannot
get all the syntax correct. Any assistance would be greatly
appreciated.

Randy

Macro syntax difficulties (long post)
 

Hi,
If "EntryDate" is named cell, then ....


res = Application.HLookup(Range("EntryDate"), LookInRange, 2, False)

HTH

"RAP" wrote:

Lee-Z,
The "EntryDate" in the following expression refers to a named cell that has
the date entered into it. The formatting for the EntryDate, as well as the
dates on the "RawDailyData" page is the same.... 5/1/2005.

Randy

res = Application.HLookup(EntryDate, LookInRange, 2, False)



"Lee-Z" wrote:

The code below doesn't provide an entrydate...or do you supply that as a
parameter for the macro?
Which dateformat do you use to supply the entrydate?

Lee-Z


"RAP" wrote in message
...
Toppers,

Thanks for the reply. I works, but not "all the way."
No matter if existing data is there or not, all I get is the "Not Found"
statement.

It acts as if the macro is not getting to the ELSE statement. Any ideas?

Thanks again,
Randy

"Toppers" wrote:

Hi,
Try this:

Dim res As Variant, EntryDate, LookInRange As Range

Set LookInRange = Worksheets("RawDailyData").Range("C1:IM2")

res = Application.HLookup(EntryDate, LookInRange, 2, False)

If IsError(res) Then ' No valid Entry Date
MsgBox "Not Found"
Else
If res < "" Then
MsgBox "true"
Else
MsgBox "False"
End If
End If

HTH

"RAP" wrote:

The suspect the source of my problem is I am running Excel 2003 and my
reference books are for Excel 2000 and Excel/VB for Windows 95.
Apparently,
different versions use different syntax. Or maybe I have Dain-Bramage
or
something..

SETUP: A user selects a date, enters data and exits the program.

The data entered is saved on a page named RawDailyData. The first ROW
has a
date for a certain number of days entered on it, beginning in cell C1

Page 1 is named StartPage, Page 2 is named DataInput, Page 3 is named
RawDailyData

When a user opens the file and enables the macros, the file opens to
the
StartPage. User sees the following:

Step 1 = Choose a date "A date is selected from a listbox in a cell
named
EntryDate"
Step 2 = Click the GO button to enter data for selected date
Step 3 = Click the Save & Exit button

The GO command button calls the GO macro. I want the GO macro to do
the
following:
1. See if data already exists on the RawDailyData page for the
selected
EntryDate.
2. If true, bring up the UserForm1

. UserForm1 has a message and a single command button, named RETURN.
The
message says to click the return button and select another EntryDate,
because
data for that date has already been entered. Currently, the button
simply
unloads the userform, essentially returning the user to the EntryDate
select
box. (I do not want the user to be able to overwrite existing data)

3. If false, (that is, no previous data has been entered for the
selected
EntryDate)
nothing happens when the GO button is clicked except that the user
is
directed
to the first data entry cell on the DataInput page to begin
entering
data.

I can achieve a TRUE/FALSE result, using a nested worksheet function
IF/HLOOKUP.
=IF(HLOOKUP(EntryDate,RawDailyData!C1:IM2,2,FALSE) <"",TRUE,FALSE)

The first line of data is saved on Row 2. If anything, even a zero, is
entered in that cell, the formula results in a TRUE. The cell must be
blank
for a FALSE.

PROBLEM: When I try to achieve the same result using a macro, I simply
cannot
get all the syntax correct. Any assistance would be greatly
appreciated.

Randy

Macro syntax difficulties (long post)
 

Thank to all. That last post did the trick. I appreciate your help.
Randy

"Toppers" wrote:

Hi,
If "EntryDate" is named cell, then ....


res = Application.HLookup(Range("EntryDate"), LookInRange, 2, False)

HTH

"RAP" wrote:

Lee-Z,
The "EntryDate" in the following expression refers to a named cell that has
the date entered into it. The formatting for the EntryDate, as well as the
dates on the "RawDailyData" page is the same.... 5/1/2005.

Randy

res = Application.HLookup(EntryDate, LookInRange, 2, False)



"Lee-Z" wrote:

The code below doesn't provide an entrydate...or do you supply that as a
parameter for the macro?
Which dateformat do you use to supply the entrydate?

Lee-Z


"RAP" wrote in message
...
Toppers,

Thanks for the reply. I works, but not "all the way."
No matter if existing data is there or not, all I get is the "Not Found"
statement.

It acts as if the macro is not getting to the ELSE statement. Any ideas?

Thanks again,
Randy

"Toppers" wrote:

Hi,
Try this:

Dim res As Variant, EntryDate, LookInRange As Range

Set LookInRange = Worksheets("RawDailyData").Range("C1:IM2")

res = Application.HLookup(EntryDate, LookInRange, 2, False)

If IsError(res) Then ' No valid Entry Date
MsgBox "Not Found"
Else
If res < "" Then
MsgBox "true"
Else
MsgBox "False"
End If
End If

HTH

"RAP" wrote:

The suspect the source of my problem is I am running Excel 2003 and my
reference books are for Excel 2000 and Excel/VB for Windows 95.
Apparently,
different versions use different syntax. Or maybe I have Dain-Bramage
or
something..

SETUP: A user selects a date, enters data and exits the program.

The data entered is saved on a page named RawDailyData. The first ROW
has a
date for a certain number of days entered on it, beginning in cell C1

Page 1 is named StartPage, Page 2 is named DataInput, Page 3 is named
RawDailyData

When a user opens the file and enables the macros, the file opens to
the
StartPage. User sees the following:

Step 1 = Choose a date "A date is selected from a listbox in a cell
named
EntryDate"
Step 2 = Click the GO button to enter data for selected date
Step 3 = Click the Save & Exit button

The GO command button calls the GO macro. I want the GO macro to do
the
following:
1. See if data already exists on the RawDailyData page for the
selected
EntryDate.
2. If true, bring up the UserForm1

. UserForm1 has a message and a single command button, named RETURN.
The
message says to click the return button and select another EntryDate,
because
data for that date has already been entered. Currently, the button
simply
unloads the userform, essentially returning the user to the EntryDate
select
box. (I do not want the user to be able to overwrite existing data)

3. If false, (that is, no previous data has been entered for the
selected
EntryDate)
nothing happens when the GO button is clicked except that the user
is
directed
to the first data entry cell on the DataInput page to begin
entering
data.

I can achieve a TRUE/FALSE result, using a nested worksheet function
IF/HLOOKUP.
=IF(HLOOKUP(EntryDate,RawDailyData!C1:IM2,2,FALSE) <"",TRUE,FALSE)

The first line of data is saved on Row 2. If anything, even a zero, is
entered in that cell, the formula results in a TRUE. The cell must be
blank
for a FALSE.

PROBLEM: When I try to achieve the same result using a macro, I simply
cannot
get all the syntax correct. Any assistance would be greatly
appreciated.

Randy