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