Copying a cell's contents into a string

M

mj0lnr

New Member
Joined
May 6, 2003
Messages
46
Hello,

I'm sure this has been asked before, but I couldn't locate it. I have a range of cells that I'd like to have checked for data (it's always going to numbers) and if it's populated with a number, it copies those contents into a hyperlink text string. The hyperlink always stays the same, only the contents of the cell will have an effect on it. The only thing I'm really asking for is help with the if, next statements.

Sincerely any appreciate help in advance -
A.
:bow:
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
Hi mj0lnr:

It is not clear what are you intending to accomplish. How about posting some sample data, and what needs to be done -- and then let us take it from there.
 
Upvote 0
The following procedure is an example of how to ONLY goto cells with numbers within a specified range. In the example the Range is D1:D30

Sub FindAllNumbersInRange()
On Error Resume Next
For Each Num In Range("D1:D30").SpecialCells(xlCellTypeConstants, 1)
MsgBox Num
Next Num
End Sub
 
Upvote 0
Rich (BB code): 
Range("H23").Select
    Selection.Copy
    Application.CutCopyMode = False
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
        "http://IM/Peregrine/WhatsMyStatus/default.asp?ProbID=IM218781&submit1=Get+Ticket+Information&SearchName="
End Sub

I have a range of cells that might or might not have data (numeric) in them and I want to be able to copy that data, should it exist, into the bolded section above.

again, thank you very much
 
Upvote 0
Hi Im not sure of the destination for the link so here are two options...

To loop through a selection and place the hyperlink in the same cell (if a number) then try this one...

Code: 
Sub HyperLinkMany()
Dim Rng As Range, c, MyAddress As String

Set Rng = Selection
For Each c In Rng
If IsNumeric(c) Then
MyAddress = "http://IM/Peregrine/WhatsMyStatus/default.asp?ProbID=IM" _
& c.Value & "&submit1=Get+Ticket+Information&SearchName="

c.Hyperlinks.Add Anchor:=c, Address:=MyAddress
End If
Next c

End Sub

If you want the selected cell to have the URL in cell H23 then try this...

Code: 
Sub HyperLinkOne()
Dim DestRng As Range, MyAddress As String

Set DestRng = Range("H23")

If Selection.Cells.Count > 1 Then
MsgBox "You can only use this with one cell"
Exit Sub
End If

If IsNumeric(Selection.Value) Then
MyAddress = "http://IM/Peregrine/WhatsMyStatus/default.asp?ProbID=IM" _
& Selection.Value & "&submit1=Get+Ticket+Information&SearchName="

DestRng.Hyperlinks.Add Anchor:=DestRng, Address:=MyAddress
End If

End Sub

hth
 
Upvote 0
Parry,

thanks so much for your help. Actually I have a range of cells starting at H23 that need to have their link set up for them. It has to be a number though in order to qualify as a link.

I hope I explained myself well enough....and again you SO MUCH for your help.

A.
 
Upvote 0
My pleasure. I think this should do it. It puts the name as just the hyperlink address but could be anything such as the number if you wanted. Let me know what you want and I'll change it for you. :-)

Just select the cells you want and run the macro & it will shoot them over to the next bank row in column H, beginning with row 23.

Code: 
Sub HyperLinkColH()
Dim MyAddress As String, NBR As Long, c

'Find next blank row in column H. Start at row 23 if its blank.
If IsEmpty(Range("h23")) Then
NBR = 23
Else
NBR = Range("h65536").End(xlUp).Row + 1
End If

'Look thru selection and place hyperlink in Col H if the
'cell is a number
For Each c In Selection
If IsNumeric(c) Then
MyAddress = "http://IM/Peregrine/WhatsMyStatus/default.asp?ProbID=IM" _
& c & "&submit1=Get+Ticket+Information&SearchName="

Cells(NBR, 8).Hyperlinks.Add Anchor:=Cells(NBR, 8), Address:=MyAddress
NBR = NBR + 1
End If

Next c

End Sub
 
Upvote 0
Parry,

it starts at H23, but stops there. All I did was copy-paste your code into my spreadsheet....am I doing something wrong?

TIA,
A.
 
Upvote 0
oh one more thing, do you have any suggestions for good book on VBA or VBScript for learning these If, Next, Else, Do, Loops? For a newbie. I'm so fascinated by all this and I would LOVE to answer someone else's question one day.

A.
 
Upvote 0
mj0lnr said:
Parry,

it starts at H23, but stops there. All I did was copy-paste your code into my spreadsheet....am I doing something wrong?

TIA,
A.
Click to expand...

Hi ya, you have to select all the cells with the numbers before you run the macro. :-)

As for a book well Ive heard good things about the MrExcel books although I havent got one myself. :oops: See the Online Store link above.
Excel 2002 Power Programming with VBA by John Walkenbach is very good. It depends if you ant pure VBA books or a mixture of formulas and VBA.

cheers :beerchug:
 
Upvote 0
You must log in or register to reply here.

Similar threads

FatalLordes
  • Question Question
Expand Cell depending on content
Replies
2
Views
298
FatalLordes
FatalLordes
C
  • Solved
Extracting data with VBA macro button from files in designated folder
2 3
Replies
20
Views
648
Micron
M
J
  • Question Question
Using VBA to insert Hyperlinks
Replies
1
Views
548
Jeffrey Mahoney
Jeffrey Mahoney
E
  • Question Question
Copy Cell as Hyperlink to another cell
Replies
1
Views
214
eli_m
E
E
  • Question Question
Copy cell contents and formatting
Replies
3
Views
244
excellence
E

Forum statistics

Threads
1,222,905
Messages
6,168,948
Members
452,227
Latest member
sam1121

Share this page

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