Formula returns #N/A

bknight
5StarLounger
Posts: 641
Joined: 08 Jul 2016, 18:53

Re: Formula returns #N/A

Post by bknight »

I'm under water.

bknight
5StarLounger
Posts: 641
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: 70747
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Formula returns #N/A

Post by HansV »

That should work
Regards,
Hans

bknight
5StarLounger
Posts: 641
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: 70747
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
Regards,
Hans

bknight
5StarLounger
Posts: 641
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: 70747
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")
Regards,
Hans

bknight
5StarLounger
Posts: 641
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: 70747
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")
Regards,
Hans

bknight
5StarLounger
Posts: 641
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: