Excel VBA, date link/display question

Ron512

Board Regular
Joined
Nov 17, 2002
Messages
98
Hello,

The below code loops thought all sheets in a workbook and creates a link to specific cells in a sheet named Index. If no value was entered in the cell a link is still created but does not display a value thereby displaying a value when the user makes an entry. This is true on all cells but those formatted for dates. If a user does not enter a value in a date formated cell a link is created on the Index sheet but the value 1/0/1900 is displayed.

How can I alter my code so no date is displayed on the Index sheet if the user does not enter a value in the date formatted cells but the link remains?

If it is necessary to post the complete procedure let me know.

Thanks
Ron


For i = Sheets("Index").Index + 1 To WB.Sheets.Count
Sheets(i).Select
CurSheet = ActiveSheet.Name
With WB.Sheets("Index")
.Cells(rCount, 1).Formula = "='" & CurSheet & "'!" & "$B$2"
.Cells(rCount, 3).Formula = "='" & CurSheet & "'!" & "$D$2"
.Cells(rCount, 10).Formula = "='" & CurSheet & "'!" & "$D$7"
End With
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
If a user does not enter a value
does it mean the user leaves the cell blank
in that case at appropriate place intruduce a conditional code
if (cell range variable)="" then <go into the next in the loop>

something like this
 
Upvote 0
Yes that is correct if the user leaves the cell blank.

if (cell range variable)="" then <go into the next in the loop>

It’s what code comes next that I need help with.

Remember the code creates a link that I want to maintain but show a blank when the user leaves the cell blank. It works fine for all cells not formatted as a date.

The cells not formatted as a date are formatted to not show zero if the user leaves the cell blank, resulting in a blank cell displayed and a link in the cell which will display a value if/when the user enters one, but the date cells display 1/0/1900 when the users leave the cell blank, they do however correctly display a date when entered.
 
Upvote 0
It is not clear what you are trying to do;
what is rcount,, is it the last row in "index" plus 1. I assumed this
I hope there is no blank in the entered data in sheet index from A1 down
on this assumption I have redesigned the macro
formatting the cells do not have any relevance. whatever the orginal entry (see the formula box) that will be the entry will be entred in cells(rcount,1)
I did not use .formula
save your orgiinla file safely somewhere and try this macro.

go thorugh the code statements carefully to understand the logic. If logic is different explains clearly

Code:
Sub testtwo()
Dim rcount  As Integer


Dim i As Integer
For i = 1 To Worksheets.Count
If Worksheets(i).Name = "index" Then GoTo line1

'MsgBox rcount
'MsgBox Worksheets("index").Cells(rcount, 1).Address
If Worksheets(i).Range("B2") <> "" Then
rcount = Worksheets("index").Range("a1").End(xlDown).Row + 1
Worksheets("index").Cells(rcount, 1) = Worksheets(i).Range("b2")
End If
If Worksheets(i).Range("d2") <> "" Then
rcount = Worksheets("index").Range("a1").End(xlDown).Row + 1
Worksheets("index").Cells(rcount, 1) = Worksheets(i).Range("D2")
End If
If Worksheets(i).Range("D7") <> "" Then
rcount = Worksheets("index").Range("a1").End(xlDown).Row + 1
Worksheets("index").Cells(rcount, 1) = Worksheets(i).Range("d7")
End If

line1:
Next

End Sub
 
Upvote 0
Vencat1962

Thanks for sticking with me on this.

Perhaps the easiest why to explain my issues is to create a new workbook, on sheet1 format column A as dates and enter some dates as test data. On sheet2 column A create a link to the cells on sheet1 column A. Sheet2 will display the date values entered on sheet1. If no date is entered on sheet1 but a link has been created for that cell on sheet2 the date value 1/0/1900 is displayed on sheet2.

How do I format the cell so the link remains but the cell displays a blank if the corresponding cell on sheet1 is left blank?

Thanks

Ron
 
Upvote 0
suppose in sheet1 the dates are entered from a1 to a10 with blanks at A4 and A8
in sheet2 A1 type or copy this formula

=IF(Sheet1!A1<>"",Sheet1!A1,"")

copy this formula down in sheet 2 from A2 to A10.
if necessary format column A of sheet 2 as a particular format of date

see what happens and is this what you want?

now type some date in A4 of sheet 1, which was originally blank see what happens to A4 of sheet 2
 
Upvote 0
That works, but I’m not sure if that’s what I’m looking for. How do I incorporate it into my code shown in my initial post? I tried a few things without luck.
 
Upvote 0
In the case I have mentioned the macro will be something like this

Code:
Sub test()
With Worksheets("sheet2")
.Range("a1").Formula = "=if(sheet1!A1<>"""",sheet1!a1,"""")"
.Range("a1").Copy .Range("a2:a10")
End With
End Sub

note those number of double quotes. and dots before "range"

this macro can be achieved if you just create a macro using my formula and modifying it from RC style to A1 style.

I am sure you can modify/incorporate the above macro to suit you.
 
Upvote 0
Re: Solved - Excel VBA, date link/display question

Vankat

Thanks for the help. This is my final working code:

.Cells(rCount, 1).Formula = "=IF('" & CurSheet & "'!" & "$A$1<>"""",'" & CurSheet & "'!" & "$A$1,"""")"

I had some trouble working out the syntax from your solution for my situation and receive some help from p45c at vbaexpress.

Thanks again for hanging with me.

ron
 
Upvote 0
I shall you a trick . It will always be tricky to put those double quotes(how many times). what you do create a macro and type the formula in the sheet and close the macro. now study the macro and modify to suit you
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top