0

I have a table with data in it and run a macro to neaten things up and then adds a hyperlink to column G but the issue is when the macro has run, the date changes from:

https://websitenamehere.com//agentView/agentname@company/2021-11-08

to

https://websitenamehere.com//agentView/agentname@company/44508

In my table, I created in column H the column for "today" and then in column G is where it puts it all together but messing up the date part.

Here is my code which i am using. Any help would be appreciated.

Sub CleanFollowUps()

Dim Lrow As Integer
Dim lCol As Integer
Dim C As Range
Lrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
lCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column

ActiveSheet.ListObjects.Add(xlSrcRange, Range(Cells(1, 1), Cells(Lrow, lCol)), , xlYes).Name = "FollowUps"
    Range("FollowUps[#All]").Select
    ActiveSheet.ListObjects("FollowUps").TableStyle = ""
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "Helper"
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "=IF(SUM([@[Due today]]+[@Late])>0,""Yes"","""")"
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "Schedule"
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "day"
    Range("H2").Select
    ActiveCell.FormulaR1C1 = "=TODAY()"
    Columns("H:H").Select
    Selection.NumberFormat = "yyyy-mm-dd"
     Range("H2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("G2").Select
    Application.CutCopyMode = False
   ActiveCell.FormulaR1C1 = _
        "=""https://websitenamehere.com/agentView/""&[@Username]&""@company/""&[@day]"
        Range("G2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    With Sheets("FU")
    For Each C In .Range("G2:G" & .Range("G" & .Rows.Count).End(xlUp).Row)
        .Hyperlinks.Add Anchor:=C, Address:=C.Value, SubAddress:=C.Value
    Next C
    End With

Application.DisplayAlerts = True
  
End Sub
Lalaland
  • 306
  • 1
  • 8
  • Your code: TLDR. But you need to concatenate your date as a text string (using the `Format` or `WorksheetFunction.Text` function). Also ,please read and apply [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Ron Rosenfeld Nov 08 '21 at 10:01
  • You need: `ActiveCell.FormulaR1C1 = _ "=""https://websitenamehere.com/agentView/""&[@Username]&""@company/""&TEXT([@day],""yyyy-mm-dd"")"` – Rory Nov 08 '21 at 10:04
  • Thank you so much, that worked perfect. I will also do a read up on that Select you mentioned, thank you so much. – Lalaland Nov 08 '21 at 11:01

0 Answers0