The following code does not work. I am trying to put the result of the
formula into cell J7. Nothing happens.

Range("J7").Value = "VLookup(Month(A4), Data!A1:B14, 2, False)& " " & YEAR(A4)

Thanks
Bill

Hi,

If you want to use VLookup (or any worksheet formula) in your code you need
to use the WorksheetFunction object

e.g,

Range("J7").Value = WorksheetFunction.VLookup( <Arguments go here)

HTH,
Matt



"Bill" wrote:

The following code does not work. I am trying to put the result of the
formula into cell J7. Nothing happens.

Range("J7").Value = "VLookup(Month(A4), Data!A1:B14, 2, False)& " " & YEAR(A4)

Thanks
Bill

Hi,

Range("J7").Value = "=VLookup(Month(A4), Data!A1:B14, 2, False)& "" "" &
YEAR(A4)"

HTH

"Matt Lunn" wrote:

Hi,

If you want to use VLookup (or any worksheet formula) in your code you need
to use the WorksheetFunction object

e.g,

Range("J7").Value = WorksheetFunction.VLookup( <Arguments go here)

HTH,
Matt



"Bill" wrote:

The following code does not work. I am trying to put the result of the
formula into cell J7. Nothing happens.

Range("J7").Value = "VLookup(Month(A4), Data!A1:B14, 2, False)& " " & YEAR(A4)

Thanks
Bill

Range("J7").Value = "VLookup(Month(A4), Data!A1:B14, 2, False)& " " &
YEAR(A4)
you have to use syntax that excel understands for vba
Range("J7").Value = application.VLookup(Month(range("A4"),etc
or put in the formula
Range("J7").formula = "=VLookup(Month(A4), Data!A1:B14, 2, False)& " " &
YEAR(A4)"
and then
Range("J7").value=Range("J7").value



--
Don Guillett
SalesAid Software

"Bill" wrote in message
...
The following code does not work. I am trying to put the result of the
formula into cell J7. Nothing happens.

Range("J7").Value = "VLookup(Month(A4), Data!A1:B14, 2, False)& " " &
YEAR(A4)

Thanks
Bill

In code you need to specify ranges as
Range("A4")
so
Month(A4) should be Month(Range("A4")
Data!A1:B14 should be Sheets("Data").Range("A1:B14")
--
steveB

Remove "AYN" from email to respond
"Bill" wrote in message
...
The following code does not work. I am trying to put the result of the
formula into cell J7. Nothing happens.

Range("J7").Value = "VLookup(Month(A4), Data!A1:B14, 2, False)& " " &
YEAR(A4)

Thanks
Bill

Steve & all

Thanks. I would appreciate any additional information you can provide. I
have not been able to get the code with the changes to work.

Bill

"STEVE BELL" wrote:

In code you need to specify ranges as
Range("A4")
so
Month(A4) should be Month(Range("A4")
Data!A1:B14 should be Sheets("Data").Range("A1:B14")
--
steveB

Remove "AYN" from email to respond
"Bill" wrote in message
...
The following code does not work. I am trying to put the result of the
formula into cell J7. Nothing happens.

Range("J7").Value = "VLookup(Month(A4), Data!A1:B14, 2, False)& " " &
YEAR(A4)

Thanks
Bill

Bll,

My oversight - forgot that you were writing code and didn't get all the
ranges set up for code:
[Note this should be on a single line, but the line continuation _ makes
Excel see it as one line; and you may not need ".Value"]

Range("A4").Value = WorksheetFunction.VLookup(Month(Range("A4")), _
Sheets("Data").Range("A1:B14"), 2, False) & " " & Year(Range("A4"))


Also - (not sure) but it looks like you just want Month Year in A4
If so - you can just format the cell
Range("A4").NumberFormat = "mmmm yyyy"
or
Range("A4").NumberFormat = "mmm yyyy"
--
steveB

Remove "AYN" from email to respond
"Bill" wrote in message
...
Steve & all

Thanks. I would appreciate any additional information you can provide. I
have not been able to get the code with the changes to work.

Bill

"STEVE BELL" wrote:

In code you need to specify ranges as
Range("A4")
so
Month(A4) should be Month(Range("A4")
Data!A1:B14 should be Sheets("Data").Range("A1:B14")
--
steveB

Remove "AYN" from email to respond
"Bill" wrote in message
...
The following code does not work. I am trying to put the result of the
formula into cell J7. Nothing happens.

Range("J7").Value = "VLookup(Month(A4), Data!A1:B14, 2, False)& " " &
YEAR(A4)

Thanks
Bill

Steve

Thanks. Thank worked perfect. Can you look at the code for months two and
three. The months do not increase. I use to add a 1 or 2 depending but it
does not work. Thanks

'first month's code
Range("L1").Value = WorksheetFunction.VLookup(Month(Range("A4")), _
Sheets("Data").Range("A1:B14"), 2, False) & " " & Year(Range("A4"))
Range("L1").NumberFormat = "mmmm yyyy"

'second month's code
Dim MTH As Variant
MTH = Range("A4").Value
If MTH = 12 Then
Range("AQ1").Value = WorksheetFunction.VLookup(Month(Range("A4") +
1), _
Sheets("Data").Range("A1:B14"), 2, False) & " " & Year(Range("A4") +
1)
Range("AQ1").NumberFormat = "mmmm yyyy"
Else
Range("AQ1").Value = WorksheetFunction.VLookup(Month(Range("A4") +
1), _
Sheets("Data").Range("A1:B14"), 2, False) & " " & Year(Range("A4"))
Range("AQ1").NumberFormat = "mmmm yyyy"
End If

'third month's code
If MTH = 11 Or 12 Then
Range("BU1").Value = WorksheetFunction.VLookup(Month(Range("A4") +
2), _
Sheets("Data").Range("A1:B14"), 2, False) & " " & Year(Range("A4") +
1)
Range("BU1").NumberFormat = "mmmm yyyy"
Else
Range("BU1").Value = WorksheetFunction.VLookup(Month(Range("A4") +
2), _
Sheets("Data").Range("A1:B14"), 2, False) & " " & Year(Range("A4"))
Range("BU1").NumberFormat = "mmmm yyyy"
End If


"STEVE BELL" wrote:

Bll,

My oversight - forgot that you were writing code and didn't get all the
ranges set up for code:
[Note this should be on a single line, but the line continuation _ makes
Excel see it as one line; and you may not need ".Value"]

Range("A4").Value = WorksheetFunction.VLookup(Month(Range("A4")), _
Sheets("Data").Range("A1:B14"), 2, False) & " " & Year(Range("A4"))


Also - (not sure) but it looks like you just want Month Year in A4
If so - you can just format the cell
Range("A4").NumberFormat = "mmmm yyyy"
or
Range("A4").NumberFormat = "mmm yyyy"
--
steveB

Remove "AYN" from email to respond
"Bill" wrote in message
...
Steve & all

Thanks. I would appreciate any additional information you can provide. I
have not been able to get the code with the changes to work.

Bill

"STEVE BELL" wrote:

In code you need to specify ranges as
Range("A4")
so
Month(A4) should be Month(Range("A4")
Data!A1:B14 should be Sheets("Data").Range("A1:B14")
--
steveB

Remove "AYN" from email to respond
"Bill" wrote in message
...
The following code does not work. I am trying to put the result of the
formula into cell J7. Nothing happens.

Range("J7").Value = "VLookup(Month(A4), Data!A1:B14, 2, False)& " " &
YEAR(A4)

Thanks
Bill

Bill,

Just a couple of minor changes:
Since it appears that MTH appears to be a number between 1 & 12, and A4
appears to be a date,
than MTH = Month(Range("A4"))
if MTH = Range("A4") it will end up being something like 38265 (the way
Excel sees a date)

If you want to write an "OR" statement, it is

If MTH = x OR MTH = y then ............

And remember that
Range("A4").Value is the same as Range("A4")
not very important but anytime you can remove a "." the code gets a little
faster....
becomes serious when your code gets long and complex.

Added a couple of variables to make the formulas a little simpler... and
remove repeat calculations.
(There are a few ways to make it even simpler - but catch up to this
first...)

don't dim variables as variant unless absolutely necessary - slows code and
uses more memory.
Since MTH runs from 1 - 14 it is easier to call it an Integer. YR is also
an Integer. And rng is
an object and needs to be "Set".

(You could also use MTH2 = MTH +2, YR2 = YR + 1) - but not important...

=======================================
Dim MTH As Integer, YR As Integer, rng As Range

MTH = Month(Range("A4"))
YR = Year(Range("A4"))
Set rng = Sheets("Data").Range("A1:B14")

'first month's code
Range("L1") = WorksheetFunction.VLookup(MTH, rng, 2, False) & " " & YR
Range("L1").NumberFormat = "mmmm yyyy"

'second month's code
If MTH = 12 Then
Range("AQ1") = WorksheetFunction.VLookup(MTH + 1, rng, 2, False) & "
" & YR + 1
Range("AQ1").NumberFormat = "mmmm yyyy"
Else
Range("AQ1") = WorksheetFunction.VLookup(MTH + 1, rng, 2, False) & "
" & YR
Range("AQ1").NumberFormat = "mmmm yyyy"
End If

'third month's code
If MTH = 11 Or MTH = 12 Then
Range("BU1") = WorksheetFunction.VLookup(MTH + 2, rng, 2, False) & "
" & YR + 1
Range("BU1").NumberFormat = "mmmm yyyy"
Else
Range("BU1") = WorksheetFunction.VLookup(MTH + 2, rng, 2, False) & "
" & YR
Range("BU1").NumberFormat = "mmmm yyyy"
End If
=========================================

--
steveB

Remove "AYN" from email to respond
"Bill" wrote in message
...
Steve

Thanks. Thank worked perfect. Can you look at the code for months two and
three. The months do not increase. I use to add a 1 or 2 depending but it
does not work. Thanks

'first month's code
Range("L1").Value = WorksheetFunction.VLookup(Month(Range("A4")), _
Sheets("Data").Range("A1:B14"), 2, False) & " " & Year(Range("A4"))
Range("L1").NumberFormat = "mmmm yyyy"

'second month's code
Dim MTH As Variant
MTH = Range("A4").Value
If MTH = 12 Then
Range("AQ1").Value = WorksheetFunction.VLookup(Month(Range("A4") +
1), _
Sheets("Data").Range("A1:B14"), 2, False) & " " & Year(Range("A4")
+
1)
Range("AQ1").NumberFormat = "mmmm yyyy"
Else
Range("AQ1").Value = WorksheetFunction.VLookup(Month(Range("A4") +
1), _
Sheets("Data").Range("A1:B14"), 2, False) & " " & Year(Range("A4"))
Range("AQ1").NumberFormat = "mmmm yyyy"
End If

'third month's code
If MTH = 11 Or 12 Then
Range("BU1").Value = WorksheetFunction.VLookup(Month(Range("A4") +
2), _
Sheets("Data").Range("A1:B14"), 2, False) & " " & Year(Range("A4")
+
1)
Range("BU1").NumberFormat = "mmmm yyyy"
Else
Range("BU1").Value = WorksheetFunction.VLookup(Month(Range("A4") +
2), _
Sheets("Data").Range("A1:B14"), 2, False) & " " & Year(Range("A4"))
Range("BU1").NumberFormat = "mmmm yyyy"
End If


"STEVE BELL" wrote:

Bll,

My oversight - forgot that you were writing code and didn't get all the
ranges set up for code:
[Note this should be on a single line, but the line continuation _ makes
Excel see it as one line; and you may not need ".Value"]

Range("A4").Value = WorksheetFunction.VLookup(Month(Range("A4")), _
Sheets("Data").Range("A1:B14"), 2, False) & " " & Year(Range("A4"))


Also - (not sure) but it looks like you just want Month Year in A4
If so - you can just format the cell
Range("A4").NumberFormat = "mmmm yyyy"
or
Range("A4").NumberFormat = "mmm yyyy"
--
steveB

Remove "AYN" from email to respond
"Bill" wrote in message
...
Steve & all

Thanks. I would appreciate any additional information you can provide.
I
have not been able to get the code with the changes to work.

Bill

"STEVE BELL" wrote:

In code you need to specify ranges as
Range("A4")
so
Month(A4) should be Month(Range("A4")
Data!A1:B14 should be Sheets("Data").Range("A1:B14")
--
steveB

Remove "AYN" from email to respond
"Bill" wrote in message
...
The following code does not work. I am trying to put the result of
the
formula into cell J7. Nothing happens.

Range("J7").Value = "VLookup(Month(A4), Data!A1:B14, 2, False)& " "
&
YEAR(A4)

Thanks
Bill

Steve

Outstanding. Thanks for your help.

Bill

"STEVE BELL" wrote:

Bill,

Just a couple of minor changes:
Since it appears that MTH appears to be a number between 1 & 12, and A4
appears to be a date,
than MTH = Month(Range("A4"))
if MTH = Range("A4") it will end up being something like 38265 (the way
Excel sees a date)

If you want to write an "OR" statement, it is

If MTH = x OR MTH = y then ............

And remember that
Range("A4").Value is the same as Range("A4")
not very important but anytime you can remove a "." the code gets a little
faster....
becomes serious when your code gets long and complex.

Added a couple of variables to make the formulas a little simpler... and
remove repeat calculations.
(There are a few ways to make it even simpler - but catch up to this
first...)

don't dim variables as variant unless absolutely necessary - slows code and
uses more memory.
Since MTH runs from 1 - 14 it is easier to call it an Integer. YR is also
an Integer. And rng is
an object and needs to be "Set".

(You could also use MTH2 = MTH +2, YR2 = YR + 1) - but not important...

=======================================
Dim MTH As Integer, YR As Integer, rng As Range

MTH = Month(Range("A4"))
YR = Year(Range("A4"))
Set rng = Sheets("Data").Range("A1:B14")

'first month's code
Range("L1") = WorksheetFunction.VLookup(MTH, rng, 2, False) & " " & YR
Range("L1").NumberFormat = "mmmm yyyy"

'second month's code
If MTH = 12 Then
Range("AQ1") = WorksheetFunction.VLookup(MTH + 1, rng, 2, False) & "
" & YR + 1
Range("AQ1").NumberFormat = "mmmm yyyy"
Else
Range("AQ1") = WorksheetFunction.VLookup(MTH + 1, rng, 2, False) & "
" & YR
Range("AQ1").NumberFormat = "mmmm yyyy"
End If

'third month's code
If MTH = 11 Or MTH = 12 Then
Range("BU1") = WorksheetFunction.VLookup(MTH + 2, rng, 2, False) & "
" & YR + 1
Range("BU1").NumberFormat = "mmmm yyyy"
Else
Range("BU1") = WorksheetFunction.VLookup(MTH + 2, rng, 2, False) & "
" & YR
Range("BU1").NumberFormat = "mmmm yyyy"
End If
=========================================

--
steveB

Remove "AYN" from email to respond
"Bill" wrote in message
...
Steve

Thanks. Thank worked perfect. Can you look at the code for months two and
three. The months do not increase. I use to add a 1 or 2 depending but it
does not work. Thanks

'first month's code
Range("L1").Value = WorksheetFunction.VLookup(Month(Range("A4")), _
Sheets("Data").Range("A1:B14"), 2, False) & " " & Year(Range("A4"))
Range("L1").NumberFormat = "mmmm yyyy"

'second month's code
Dim MTH As Variant
MTH = Range("A4").Value
If MTH = 12 Then
Range("AQ1").Value = WorksheetFunction.VLookup(Month(Range("A4") +
1), _
Sheets("Data").Range("A1:B14"), 2, False) & " " & Year(Range("A4")
+
1)
Range("AQ1").NumberFormat = "mmmm yyyy"
Else
Range("AQ1").Value = WorksheetFunction.VLookup(Month(Range("A4") +
1), _
Sheets("Data").Range("A1:B14"), 2, False) & " " & Year(Range("A4"))
Range("AQ1").NumberFormat = "mmmm yyyy"
End If

'third month's code
If MTH = 11 Or 12 Then
Range("BU1").Value = WorksheetFunction.VLookup(Month(Range("A4") +
2), _
Sheets("Data").Range("A1:B14"), 2, False) & " " & Year(Range("A4")
+
1)
Range("BU1").NumberFormat = "mmmm yyyy"
Else
Range("BU1").Value = WorksheetFunction.VLookup(Month(Range("A4") +
2), _
Sheets("Data").Range("A1:B14"), 2, False) & " " & Year(Range("A4"))
Range("BU1").NumberFormat = "mmmm yyyy"
End If


"STEVE BELL" wrote:

Bll,

My oversight - forgot that you were writing code and didn't get all the
ranges set up for code:
[Note this should be on a single line, but the line continuation _ makes
Excel see it as one line; and you may not need ".Value"]

Range("A4").Value = WorksheetFunction.VLookup(Month(Range("A4")), _
Sheets("Data").Range("A1:B14"), 2, False) & " " & Year(Range("A4"))


Also - (not sure) but it looks like you just want Month Year in A4
If so - you can just format the cell
Range("A4").NumberFormat = "mmmm yyyy"
or
Range("A4").NumberFormat = "mmm yyyy"
--
steveB

Remove "AYN" from email to respond
"Bill" wrote in message
...
Steve & all

Thanks. I would appreciate any additional information you can provide.
I
have not been able to get the code with the changes to work.

Bill

"STEVE BELL" wrote:

In code you need to specify ranges as
Range("A4")
so
Month(A4) should be Month(Range("A4")
Data!A1:B14 should be Sheets("Data").Range("A1:B14")
--
steveB

Remove "AYN" from email to respond
"Bill" wrote in message
...
The following code does not work. I am trying to put the result of
the
formula into cell J7. Nothing happens.

Range("J7").Value = "VLookup(Month(A4), Data!A1:B14, 2, False)& " "
&
YEAR(A4)

Thanks
Bill

Bill,

Glad it worked!

Keep on Exceling...

--
steveB

Remove "AYN" from email to respond
"Bill" wrote in message
...
Steve

Outstanding. Thanks for your help.

Bill

"STEVE BELL" wrote:

Bill,

Just a couple of minor changes:
Since it appears that MTH appears to be a number between 1 & 12, and A4
appears to be a date,
than MTH = Month(Range("A4"))
if MTH = Range("A4") it will end up being something like 38265 (the way
Excel sees a date)

If you want to write an "OR" statement, it is

If MTH = x OR MTH = y then ............

And remember that
Range("A4").Value is the same as Range("A4")
not very important but anytime you can remove a "." the code gets a
little
faster....
becomes serious when your code gets long and complex.

Added a couple of variables to make the formulas a little simpler... and
remove repeat calculations.
(There are a few ways to make it even simpler - but catch up to this
first...)

don't dim variables as variant unless absolutely necessary - slows code
and
uses more memory.
Since MTH runs from 1 - 14 it is easier to call it an Integer. YR is
also
an Integer. And rng is
an object and needs to be "Set".

(You could also use MTH2 = MTH +2, YR2 = YR + 1) - but not important...

=======================================
Dim MTH As Integer, YR As Integer, rng As Range

MTH = Month(Range("A4"))
YR = Year(Range("A4"))
Set rng = Sheets("Data").Range("A1:B14")

'first month's code
Range("L1") = WorksheetFunction.VLookup(MTH, rng, 2, False) & " " &
YR
Range("L1").NumberFormat = "mmmm yyyy"

'second month's code
If MTH = 12 Then
Range("AQ1") = WorksheetFunction.VLookup(MTH + 1, rng, 2, False)
& "
" & YR + 1
Range("AQ1").NumberFormat = "mmmm yyyy"
Else
Range("AQ1") = WorksheetFunction.VLookup(MTH + 1, rng, 2, False)
& "
" & YR
Range("AQ1").NumberFormat = "mmmm yyyy"
End If

'third month's code
If MTH = 11 Or MTH = 12 Then
Range("BU1") = WorksheetFunction.VLookup(MTH + 2, rng, 2, False)
& "
" & YR + 1
Range("BU1").NumberFormat = "mmmm yyyy"
Else
Range("BU1") = WorksheetFunction.VLookup(MTH + 2, rng, 2, False)
& "
" & YR
Range("BU1").NumberFormat = "mmmm yyyy"
End If
=========================================

--
steveB

Remove "AYN" from email to respond
"Bill" wrote in message
...
Steve

Thanks. Thank worked perfect. Can you look at the code for months two
and
three. The months do not increase. I use to add a 1 or 2 depending but
it
does not work. Thanks

'first month's code
Range("L1").Value = WorksheetFunction.VLookup(Month(Range("A4")), _
Sheets("Data").Range("A1:B14"), 2, False) & " " & Year(Range("A4"))
Range("L1").NumberFormat = "mmmm yyyy"

'second month's code
Dim MTH As Variant
MTH = Range("A4").Value
If MTH = 12 Then
Range("AQ1").Value = WorksheetFunction.VLookup(Month(Range("A4")
+
1), _
Sheets("Data").Range("A1:B14"), 2, False) & " " &
Year(Range("A4")
+
1)
Range("AQ1").NumberFormat = "mmmm yyyy"
Else
Range("AQ1").Value = WorksheetFunction.VLookup(Month(Range("A4")
+
1), _
Sheets("Data").Range("A1:B14"), 2, False) & " " &
Year(Range("A4"))
Range("AQ1").NumberFormat = "mmmm yyyy"
End If

'third month's code
If MTH = 11 Or 12 Then
Range("BU1").Value = WorksheetFunction.VLookup(Month(Range("A4")
+
2), _
Sheets("Data").Range("A1:B14"), 2, False) & " " &
Year(Range("A4")
+
1)
Range("BU1").NumberFormat = "mmmm yyyy"
Else
Range("BU1").Value = WorksheetFunction.VLookup(Month(Range("A4")
+
2), _
Sheets("Data").Range("A1:B14"), 2, False) & " " &
Year(Range("A4"))
Range("BU1").NumberFormat = "mmmm yyyy"
End If


"STEVE BELL" wrote:

Bll,

My oversight - forgot that you were writing code and didn't get all
the
ranges set up for code:
[Note this should be on a single line, but the line continuation _
makes
Excel see it as one line; and you may not need ".Value"]

Range("A4").Value = WorksheetFunction.VLookup(Month(Range("A4")), _
Sheets("Data").Range("A1:B14"), 2, False) & " " &
Year(Range("A4"))


Also - (not sure) but it looks like you just want Month Year in A4
If so - you can just format the cell
Range("A4").NumberFormat = "mmmm yyyy"
or
Range("A4").NumberFormat = "mmm yyyy"
--
steveB

Remove "AYN" from email to respond
"Bill" wrote in message
...
Steve & all

Thanks. I would appreciate any additional information you can
provide.
I
have not been able to get the code with the changes to work.

Bill

"STEVE BELL" wrote:

In code you need to specify ranges as
Range("A4")
so
Month(A4) should be Month(Range("A4")
Data!A1:B14 should be Sheets("Data").Range("A1:B14")
--
steveB

Remove "AYN" from email to respond
"Bill" wrote in message
...
The following code does not work. I am trying to put the result
of
the
formula into cell J7. Nothing happens.

Range("J7").Value = "VLookup(Month(A4), Data!A1:B14, 2, False)& "
"
&
YEAR(A4)

Thanks
Bill

Steve

I thought I might ask you for a little more assistance. You last help was
superb and the code was so clean and worked flawlessly. This is very
difficult and may require much time so I understand if you want to pass. I
am working on it. Here is a what needs to occur if you decide to help with
this difficult WorksheetFunction.VLookup function. I can send you my
worksheet if you want to look at it.

I have the target date in range(€œ$A$4). Row B3 starts off with 1 (first day
of month) and continues for three months (B3 = 1, C3 = 2, D3 =3, E3 = $ and
so on).
I need the following to occur. When the date in A4 changes I need code that
will scan the range from B3 to the columns end. Example endCol = Cells(3,
Columns.Count).End (xlToLeft) .Column €˜tells me the number of days

Then for each row add the date in A4 plus the number in row 3 of column
minus one. Example Datef = $A$4 + G$3 €“ 1 €˜gives me the actual date for
this row. Then I need it to lookup that date in the range(j1:k20) on another
sheet("data").

Range("Row = 10, ??-Depends on column") = WorksheetFunction.VLookup(Datef,
rng, 2, False)

I need the result for column k enter into an array and placed in that column
starting in row 10.

Example if the results from column k is retire then row 10 = €œr€�, row 11 =
€œe€�, row 12 = €œt€�, row 13 = €œI€�, row 15 = €œr€�, row 16 = €œe€�.

As an overall example

If€¦.

A4 = 1 Jun 05 then the value in K3 would be ten
K3 = 10 Jun 05 €˜ determined using $A$4 + K$3 €“ 1

Then on Sheet(€œData€�)

The vlookup matches value from K3 to J7, H7 value equals €œG3 Retire€�.

The resulting value is as follows

K10= G
K11= 3
K12=
K13= R
K14= e
K15= t
K16= i
K17= r
K18 = e

Thanks either way for all your help.
Bill

Bill,

Sounds fair to me:


Sorry for the delay - went to the fair...

--
steveB

Remove "AYN" from email to respond
"Bill" wrote in message
...
Steve

I thought I might ask you for a little more assistance. You last help was
superb and the code was so clean and worked flawlessly. This is very
difficult and may require much time so I understand if you want to pass.
I
am working on it. Here is a what needs to occur if you decide to help with
this difficult WorksheetFunction.VLookup function. I can send you my
worksheet if you want to look at it.

I have the target date in range("$A$4). Row B3 starts off with 1 (first
day
of month) and continues for three months (B3 = 1, C3 = 2, D3 =3, E3 = $
and
so on).
I need the following to occur. When the date in A4 changes I need code
that
will scan the range from B3 to the column's end. Example endCol = Cells(3,
Columns.Count).End (xlToLeft) .Column 'tells me the number of days

Then for each row add the date in A4 plus the number in row 3 of column
minus one. Example Datef = $A$4 + G$3 - 1 'gives me the actual date for
this row. Then I need it to lookup that date in the range(j1:k20) on
another
sheet("data").

Range("Row = 10, ??-Depends on column") = WorksheetFunction.VLookup(Datef,
rng, 2, False)

I need the result for column k enter into an array and placed in that
column
starting in row 10.

Example if the results from column k is retire then row 10 = "r", row 11 =
"e", row 12 = "t", row 13 = "I", row 15 = "r", row 16 = "e".

As an overall example

If..

A4 = 1 Jun 05 then the value in K3 would be ten
K3 = 10 Jun 05 ' determined using $A$4 + K$3 - 1

Then on Sheet("Data")

The vlookup matches value from K3 to J7, H7 value equals "G3 Retire".

The resulting value is as follows

K10= G
K11= 3
K12=
K13= R
K14= e
K15= t
K16= i
K17= r
K18 = e

Thanks either way for all your help.
Bill

Bill,

Are you still out there?
Do you still want help?



--
steveB

Remove "AYN" from email to respond
"STEVE BELL" wrote in message
news:LwxGe.911$Bx5.443@trnddc09...
Bill,

Sounds fair to me:


Sorry for the delay - went to the fair...

--
steveB

Remove "AYN" from email to respond
"Bill" wrote in message
...
Steve

I thought I might ask you for a little more assistance. You last help was
superb and the code was so clean and worked flawlessly. This is very
difficult and may require much time so I understand if you want to pass.
I
am working on it. Here is a what needs to occur if you decide to help
with
this difficult WorksheetFunction.VLookup function. I can send you my
worksheet if you want to look at it.

I have the target date in range("$A$4). Row B3 starts off with 1 (first
day
of month) and continues for three months (B3 = 1, C3 = 2, D3 =3, E3 = $
and
so on).
I need the following to occur. When the date in A4 changes I need code
that
will scan the range from B3 to the column's end. Example endCol =
Cells(3,
Columns.Count).End (xlToLeft) .Column 'tells me the number of days

Then for each row add the date in A4 plus the number in row 3 of column
minus one. Example Datef = $A$4 + G$3 - 1 'gives me the actual date for
this row. Then I need it to lookup that date in the range(j1:k20) on
another
sheet("data").

Range("Row = 10, ??-Depends on column") =
WorksheetFunction.VLookup(Datef,
rng, 2, False)

I need the result for column k enter into an array and placed in that
column
starting in row 10.

Example if the results from column k is retire then row 10 = "r", row 11
=
"e", row 12 = "t", row 13 = "I", row 15 = "r", row 16 = "e".

As an overall example

If..

A4 = 1 Jun 05 then the value in K3 would be ten
K3 = 10 Jun 05 ' determined using $A$4 + K$3 - 1

Then on Sheet("Data")

The vlookup matches value from K3 to J7, H7 value equals "G3 Retire".

The resulting value is as follows

K10= G
K11= 3
K12=
K13= R
K14= e
K15= t
K16= i
K17= r
K18 = e

Thanks either way for all your help.
Bill

Steve

Yes, I would still like some help if possible. I emailed the file to you.
Thanks

Bill

"STEVE BELL" wrote:

Bill,

Are you still out there?
Do you still want help?



--
steveB

Remove "AYN" from email to respond
"STEVE BELL" wrote in message
news:LwxGe.911$Bx5.443@trnddc09...
Bill,

Sounds fair to me:


Sorry for the delay - went to the fair...

--
steveB

Remove "AYN" from email to respond
"Bill" wrote in message
...
Steve

I thought I might ask you for a little more assistance. You last help was
superb and the code was so clean and worked flawlessly. This is very
difficult and may require much time so I understand if you want to pass.
I
am working on it. Here is a what needs to occur if you decide to help
with
this difficult WorksheetFunction.VLookup function. I can send you my
worksheet if you want to look at it.

I have the target date in range("$A$4). Row B3 starts off with 1 (first
day
of month) and continues for three months (B3 = 1, C3 = 2, D3 =3, E3 = $
and
so on).
I need the following to occur. When the date in A4 changes I need code
that
will scan the range from B3 to the column's end. Example endCol =
Cells(3,
Columns.Count).End (xlToLeft) .Column 'tells me the number of days

Then for each row add the date in A4 plus the number in row 3 of column
minus one. Example Datef = $A$4 + G$3 - 1 'gives me the actual date for
this row. Then I need it to lookup that date in the range(j1:k20) on
another
sheet("data").

Range("Row = 10, ??-Depends on column") =
WorksheetFunction.VLookup(Datef,
rng, 2, False)

I need the result for column k enter into an array and placed in that
column
starting in row 10.

Example if the results from column k is retire then row 10 = "r", row 11
=
"e", row 12 = "t", row 13 = "I", row 15 = "r", row 16 = "e".

As an overall example

If..

A4 = 1 Jun 05 then the value in K3 would be ten
K3 = 10 Jun 05 ' determined using $A$4 + K$3 - 1

Then on Sheet("Data")

The vlookup matches value from K3 to J7, H7 value equals "G3 Retire".

The resulting value is as follows

K10= G
K11= 3
K12=
K13= R
K14= e
K15= t
K16= i
K17= r
K18 = e

Thanks either way for all your help.
Bill

Bill,

I screwed up... my email is


(the 459 is from an older user name)

Please try again...

--
steveB

Remove "AYN" from email to respond
"Bill" wrote in message
...
Steve

Yes, I would still like some help if possible. I emailed the file to you.
Thanks

Bill

"STEVE BELL" wrote:

Bill,

Are you still out there?
Do you still want help?



--
steveB

Remove "AYN" from email to respond
"STEVE BELL" wrote in message
news:LwxGe.911$Bx5.443@trnddc09...
Bill,

Sounds fair to me:


Sorry for the delay - went to the fair...

--
steveB

Remove "AYN" from email to respond
"Bill" wrote in message
...
Steve

I thought I might ask you for a little more assistance. You last help
was
superb and the code was so clean and worked flawlessly. This is very
difficult and may require much time so I understand if you want to
pass.
I
am working on it. Here is a what needs to occur if you decide to help
with
this difficult WorksheetFunction.VLookup function. I can send you my
worksheet if you want to look at it.

I have the target date in range("$A$4). Row B3 starts off with 1
(first
day
of month) and continues for three months (B3 = 1, C3 = 2, D3 =3, E3 =
$
and
so on).
I need the following to occur. When the date in A4 changes I need
code
that
will scan the range from B3 to the column's end. Example endCol =
Cells(3,
Columns.Count).End (xlToLeft) .Column 'tells me the number of days

Then for each row add the date in A4 plus the number in row 3 of
column
minus one. Example Datef = $A$4 + G$3 - 1 'gives me the actual date
for
this row. Then I need it to lookup that date in the range(j1:k20) on
another
sheet("data").

Range("Row = 10, ??-Depends on column") =
WorksheetFunction.VLookup(Datef,
rng, 2, False)

I need the result for column k enter into an array and placed in that
column
starting in row 10.

Example if the results from column k is retire then row 10 = "r", row
11
=
"e", row 12 = "t", row 13 = "I", row 15 = "r", row 16 = "e".

As an overall example

If..

A4 = 1 Jun 05 then the value in K3 would be ten
K3 = 10 Jun 05 ' determined using $A$4 + K$3 - 1

Then on Sheet("Data")

The vlookup matches value from K3 to J7, H7 value equals "G3 Retire".

The resulting value is as follows

K10= G
K11= 3
K12=
K13= R
K14= e
K15= t
K16= i
K17= r
K18 = e

Thanks either way for all your help.
Bill

Steve

I resent it to you address. Thanks again.

Bill

"STEVE BELL" wrote:

Bill,

I screwed up... my email is


(the 459 is from an older user name)

Please try again...

--
steveB

Remove "AYN" from email to respond
"Bill" wrote in message
...
Steve

Yes, I would still like some help if possible. I emailed the file to you.
Thanks

Bill

"STEVE BELL" wrote:

Bill,

Are you still out there?
Do you still want help?



--
steveB

Remove "AYN" from email to respond
"STEVE BELL" wrote in message
news:LwxGe.911$Bx5.443@trnddc09...
Bill,

Sounds fair to me:


Sorry for the delay - went to the fair...

--
steveB

Remove "AYN" from email to respond
"Bill" wrote in message
...
Steve

I thought I might ask you for a little more assistance. You last help
was
superb and the code was so clean and worked flawlessly. This is very
difficult and may require much time so I understand if you want to
pass.
I
am working on it. Here is a what needs to occur if you decide to help
with
this difficult WorksheetFunction.VLookup function. I can send you my
worksheet if you want to look at it.

I have the target date in range("$A$4). Row B3 starts off with 1
(first
day
of month) and continues for three months (B3 = 1, C3 = 2, D3 =3, E3 =
$
and
so on).
I need the following to occur. When the date in A4 changes I need
code
that
will scan the range from B3 to the column's end. Example endCol =
Cells(3,
Columns.Count).End (xlToLeft) .Column 'tells me the number of days

Then for each row add the date in A4 plus the number in row 3 of
column
minus one. Example Datef = $A$4 + G$3 - 1 'gives me the actual date
for
this row. Then I need it to lookup that date in the range(j1:k20) on
another
sheet("data").

Range("Row = 10, ??-Depends on column") =
WorksheetFunction.VLookup(Datef,
rng, 2, False)

I need the result for column k enter into an array and placed in that
column
starting in row 10.

Example if the results from column k is retire then row 10 = "r", row
11
=
"e", row 12 = "t", row 13 = "I", row 15 = "r", row 16 = "e".

As an overall example

If..

A4 = 1 Jun 05 then the value in K3 would be ten
K3 = 10 Jun 05 ' determined using $A$4 + K$3 - 1

Then on Sheet("Data")

The vlookup matches value from K3 to J7, H7 value equals "G3 Retire".

The resulting value is as follows

K10= G
K11= 3
K12=
K13= R
K14= e
K15= t
K16= i
K17= r
K18 = e

Thanks either way for all your help.
Bill