Formula returns #N/A

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Formula returns #N/A

Post by bknight »

I'm under water.

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Formula returns #N/A

Post by bknight »

For a short term fix how would I set the value of H2 = to 04/08/21 (today) then 04/08/21 strDate
so that the lookup features might find the correct day in each of the worksheets?

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Formula returns #N/A

Post by HansV »

That should work
Best wishes,
Hans

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Formula returns #N/A

Post by bknight »

What would be the correct coding?

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Formula returns #N/A

Post by HansV »

Just use something like

Code: Select all

    Range("H2").Value = Date
Best wishes,
Hans

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Formula returns #N/A

Post by bknight »

Doesn't it have to have modifiers so that it is in the same form as 04/08/21 so the lookup works properly?

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Formula returns #N/A

Post by HansV »

You'll have to test that. If it doesn't work this way, use

Code: Select all

    Range("H2").Value = Format(Date, "mm/dd/yy")
Best wishes,
Hans

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Formula returns #N/A

Post by bknight »

Well I fumbled around with maybe 5-6 iterations prior to getting some success. I do have a question though.
What is the correct coding for "inserting" Date into H2 on the Data worksheet from anywhere in the workbook. In other words whichever sheet has focus.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Formula returns #N/A

Post by HansV »

Specify the worksheet explicitly:

Code: Select all

    Worksheets("Data").Range("H2").Value = Format(Date, "mm/dd/yy")
Best wishes,
Hans

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Formula returns #N/A

Post by bknight »

Ok, then I added

Code: Select all

strDate = Worksheets("Data").Range("H2").Value
Everything compiled. :cheers: