Excel VBA macro reading one column with differing text

Question

I was tasked with creating a code that will check to see if internal hyperlinks in an excel spreadsheet worked. This code first changes the formulas that were on the spreadsheet and makes them actual hyperlinks (they were originally formulas linking the locations together). The problem that I have now is that I want to create hyperlinks ONLY if Column S has text. If it doesn't, I don't want the "E-COPY" text to be displayed. All of the text in Column S varies (not one line has the same characters), which is why I'm drawing a blank is to how I tell the program to only continue if it has any text, not anything specific. I am working with Excel 2016.

Also, I am doing this to 71935 and counting rows; is there a limit to how many it can go through? If so, what can I do about it?

Thank you!

Sub CreateHyperlinks()
Dim FN As Variant

Dim Path As Variant
Dim count As Variant

Sheets(1).Activate
count = WorksheetFunction.CountA(Sheets(1).Range("A:A"))


For i = 2 To count

If Range("AM" & i).Value = "Yes" And Columns("S") =  Then 
Range("E" & i).Value = ""

Path = Sheets(1).Range("R" & i).Value
FN = Sheets(1).Range("S" & i).Value


    Sheets(1).Range("E" & i).Select
    Selection.ClearFormats
    Selection.Hyperlinks.Add Anchor:=Selection, Address:=Path & FN, TextToDisplay:="E-COPY"

    Range("AM" & i).Value = " "

End If

Next i


End Sub

Any text in S vs. empty? or vs. a number/date/etc?

Tim Williams
– Tim Williams

2018-07-27 18:24:13 +00:00
Commented Jul 27, 2018 at 18:24 — Tim Williams
– Tim Williams, Commented Jul 27, 2018 at 18:24

Tim Williams · Accepted Answer · 2018-07-27 18:25:32Z

2

If you just need to check for any content in ColS then:

If Range("AM" & i).Value = "Yes" And Len(Range("S" & i).Value) > 0 Then

answered Jul 27, 2018 at 18:25

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jcrizk · Accepted Answer · 2018-07-27 18:37:55Z

2

Few things:

'make a reference to the sheet you're working with
Dim ws As Worksheet
Dim wb As Workbook

Set wb = Excel.Application.ThisWorkbook
Set ws = wb.Worksheets(1)

'gets the absolute last row with data in it // ignores empty cells
count = ws.UsedRange.Rows.Count

personally, i hate working with named ranges, so i would suggest setting range references like so

what you wrote

Path = Sheets(1).Range("R" & i).Value

what i believe it should look like

Path = ws.Cells(i, 18).Value

if you want to test the type when working with variants, try this:

'tests the type associated with the variant. an 8 = string
If VarType(ws.Cells(i, 19).Value) = 8 Then
    'do your thing

'tests if the value is null
ElseIf VarType(ws.Cells(i, 19).Value) = 0 Then
    'do your other thing

here's a list of the vartype enumeration to help you out.

hope it helps!

answered Jul 27, 2018 at 18:37

jcrizk

6251 gold badge5 silver badges15 bronze badges

8 Comments

Mathieu Guindon Over a year ago

Couldn't reply to your previous ping (answer was deleted), so here: yes, I did read that link which I fetched from my bookmarks - the now-deleted question was closed by myself and the moderator that handled the NAA flag I cast on the answer. Not sure what you were insinuating there.

Mathieu Guindon Over a year ago

Now while I'm here, why not use the VbVarType enum members (defined in the VBA standard library), instead of using magic numbers and comments to explain them? vbString is self-explanatory, 8 isn't. ;-)

jcrizk Over a year ago

Basically, I copied from the article you linked and in summary it said that bad questions elicit bad answers. I mean, there are numerous ways to skin a cat, and if the person is asking "how do i skin a cat? i have no experience" then the reasonable answer is to give them relevant tutorials.

Mathieu Guindon Over a year ago

That's the nice thing to do, indeed - and with 73 rep the author of that answer should have done that in a comment while highlighting that the question is off-topic ;-)

jcrizk Over a year ago

True that vbString is self-explanatory, however I prefer to use numbers instead of text. Just my thing.

|

Collectives™ on Stack Overflow

Excel VBA macro reading one column with differing text

2 Answers 2

Comments

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related