1

Good evening all,

I am writing an excel program to run phone numbers through our own intranet. After entering 1 through 50 phone numbers, behind the scenes, the number A1 is broken up into B1, C1 and D1 (B1 is the area code, C1 is the middle three digits and D1 is the last four) then recreated in E1 without special characters. Excel will create a Hyperlink for each number in F1, G1, H1 and I1 (I split them so I can search 4 ways, 10 digits with hypens, 10 digits no hypens, 7 digits with hypens and 7 digits no hypens.) Each F1, G1, H1 and I1 cell looks like =Hyperlink("intranetsite" & B1 & C1 & D1, ""&B1&""&C1&""&D1&"") (or a version of that depending on 7 or 10 or hypens). My problem is that the macro will count every cell with a created Hyperlink (it is looking for "=Hyperlink(", which is all of them even if a number isn't entered, it just doesn't show anything, but it still has the formula.

Is there a way to count the cells in column A that have a number inputted into it and if a number is there, then have F1, G1, H1 and I1 =HYPERLINK...., else leave blank?

This link is where I found code to search for hyperlinks. See answer by @Siddharth Rout from 09/30/13 at 10:12: Open Hyperlinks Using VBA in Excel (Runtime Error 9)

Please see latest comment for more details. Would appreciate any help. Thanks!

Community
  • 1
  • 1
Marsh Mi
  • 11
  • 3
  • **Please update this post with a specific example** – Gary's Student Apr 21 '14 at 11:02
  • A specific example of what? – Marsh Mi Apr 21 '14 at 20:36
  • .......cell contents and expected result............ – Gary's Student Apr 21 '14 at 20:41
  • Someone will input cells A1:A50 with a phone number or not. Cells G1:G50 ALWAYS is =Hyperlink("intranetsite" & B1 & C1 & D1, ""&B1&""&C1&""&D1&"") Is there a function that will search cells A1:A50 and if there is a 10 digit phone number, then the corresponding G cell will be populated with =Hyperlink("intranetsite" & B1 & C1 & D1, ""&B1&""&C1&""&D1&"") otherwise, if no number is present, it will leave the corresponding G cell blank. I tried =IF(A1="","",HYPERLINK("http://intelweb/results.aspx?k=" & B1 & C1 & D1, ""&B1&""&C1&""&D1&"")) but the macro is looking for "=HYPERLINK(". – Marsh Mi Apr 22 '14 at 03:52

0 Answers0