Applying macros to multiple cells at once

W

WordIII

New Member
Joined
Oct 15, 2021
Messages
5
Office Version
  1. 365
Platform
  1. MacOS
I am VERY new to Macro's and unfortunately don't use them enough, I apologize ahead of time if I am not explaining this correctly. I have spreadsheets that require me to insert hyperlinks to certain documents that are in different folders in Dropbox. The documents are all formatted in VOLUME.PAGE format, the spreadsheets all have a column for the volume (K) and a column for the page (L). This is the macro that I use which works fine but I have to run in each cell separately which takes time when the spreadsheet is larger. Is there a way to change the macro so that I don't have to apply to each cell individually?

VBA Code: 
Sub setHyperlink()
Dim nmbRow, nmbCols As Integer
Dim strFullPath, strA1, strB1, strFT, strFolder, strHPL, strHPLO As String
nmbRow = 0
nmbCols = 1
strA1 = ActiveCell.Text
strB1 = ActiveCell.Offset(nmbRow, nmbCols).Text
strFT = ".pdf"
strFolder = "file:///Users/wordsherrill/Dropbox/COUNTY/Wells Area/14S14E Section/Documents/"
strFullPath = strFolder & strA1 & "." & strB1 & strFT
strHPL = "=hyperlink(" & """" & strFullPath & """,""" & strA1 & """)"
strHPLO = "=hyperlink(" & """" & strFullPath & """,""" & strB1 & """)"

ActiveCell.Formula = strHPL
ActiveCell.Offset(nmbRow, nmbCols).Formula = strHPLO
End Sub
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
Welcome to the Board!

You need to provide is with more information (it is difficult to do much with it without knowing what yur data looks like).
It would be helpful to see an example of the pertinent data sheets, what cells exactly you want to apply this to, and what you would like your expected output to look like.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 
Upvote 0
Thank you,

Here is a mock up with the Data. Hopefully I did this right.

EXAMPLE.xlsm
HIJKL
21/22/201561681/28/1920
31/23/19201652197/15/1920
41/29/19231984363/8/1923
54/25/192321442212/11/1928
65/5/1925792395/7/1925
75/9/1925341265/16/1925
85/11/1925341275/16/1925
99/26/19271767210/11/1927
1010/17/192717635411/18/1927
118/16/19282042678/16/1928
124/26/19292304686/4/1929
131/4/19323332834/21/1932
144/26/1935404556/4/1935
155/21/19354034306/4/1935
164/13/19425921899/2/1942
174/8/19548831114/15/1954
184/15/19559093924/25/1955
198/4/19559164418/5/1955
203/19/19569323903/20/1956
215/21/19589923585/22/1958
227/14/195910301798/18/1959
237/11/1989163012368/22/1989
247/9/199016537247/20/1990
256/1/1992171550511/10/1992
2611/12/2001220929911/14/2001
2711/12/2001209087011/14/2001
2811/12/2001209086911/14/2001
Runsheet
Cell Formulas
RangeFormula
J2J2=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/156.168.pdf","156")
K2K2=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/156.168.pdf","168")
J3J3=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/165.219.pdf","165")
K3K3=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/165.219.pdf","219")
J4J4=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/198.436.pdf","198")
K4K4=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/198.436.pdf","436")
J5J5=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/214.422.pdf","214")
K5K5=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/214.422.pdf","422")
J6J6=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/79.239.pdf","79")
K6K6=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/79.239.pdf","239")
J7J7=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/34.126.pdf","34")
K7K7=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/34.126.pdf","126")
J8J8=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/34.127.pdf","34")
K8K8=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/34.127.pdf","127")
J9J9=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/176.72.pdf","176")
K9K9=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/176.72.pdf","72")
J10J10=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/176.354.pdf","176")
K10K10=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/176.354.pdf","354")
J11J11=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/204.267.pdf","204")
K11K11=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/204.267.pdf","267")
J12J12=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/230.468.pdf","230")
K12K12=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/230.468.pdf","468")
J13J13=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/333.283.pdf","333")
K13K13=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/333.283.pdf","283")
J14J14=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/404.55.pdf","404")
K14K14=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/404.55.pdf","55")
J15J15=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/403.430.pdf","403")
K15K15=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/403.430.pdf","430")
J16J16=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/592.189.pdf","592")
K16K16=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/592.189.pdf","189")
J17J17=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/883.111.pdf","883")
K17K17=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/883.111.pdf","111")
J18J18=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/909.392.pdf","909")
K18K18=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/909.392.pdf","392")
J19J19=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/916.441.pdf","916")
K19K19=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/916.441.pdf","441")
J20J20=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/932.390.pdf","932")
K20K20=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/932.390.pdf","390")
J21J21=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/992.358.pdf","992")
K21K21=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/992.358.pdf","358")
J22J22=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/1030.179.pdf","1030")
K22K22=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/1030.179.pdf","179")
J23J23=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/1630.1236.pdf","1630")
K23K23=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/1630.1236.pdf","1236")
J24J24=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/1653.724.pdf","1653")
K24K24=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/1653.724.pdf","724")
J25J25=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/1715.505.pdf","1715")
K25K25=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/1715.505.pdf","505")
J26J26=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/2209.299.pdf","2209")
K26K26=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/2209.299.pdf","299")
J27J27=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/2090.870.pdf","2090")
K27K27=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/2090.870.pdf","870")
J28J28=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/2090.869.pdf","2090")
K28K28=HYPERLINK("file:///Users/wordsherrill/Dropbox/OXY IMPERIAL COUNTY/Mesquite Wells Area/14S14E Section 28/APN 040350004 COAST IMPERIAL/Documents/2090.869.pdf","869")
 
Upvote 0
So when you run the code for "one cell at a time", which cell are you on?
Is it a cell in column J or a cell in column K?
 
Upvote 0
OK. First a note. The following does not do what you think it does:
VBA Code: 
Dim strFullPath, strA1, strB1, strFT, strFolder, strHPL, strHPLO As String
That will declare strHPLO as String, but all the other ones will be declated to be Variant.
Each variable needs to be declared explicitly.

Now, on to your problem. I think the following code should update all your entries in column J, starting from row 2 and going to the last row.
(Note that I added some comments in green to indicate what those lines I added are doing).
VBA Code: 
Sub setHyperlink()

    Dim nmbRow As Long, nmbCols As Long
    Dim strFullPath As String, strA1 As String, strB1 As String, strFT As String
    Dim strFolder As String, strHPL As String, strHPLO As String
    Dim lr As Long, r As Long

'   Set static variables
    nmbRow = 0
    nmbCols = 1
    strFT = ".pdf"
    strFolder = "file:///Users/wordsherrill/Dropbox/COUNTY/Wells Area/14S14E Section/Documents/"
   
'   Find last row in column J with data
    lr = Cells(Rows.Count, "J").End(xlUp).Row

'   Loop through all cells in column J starting with row 2
    For r = 2 To lr
        strA1 = Cells(r, "J").Text
        strB1 = Cells(r, "J").Offset(nmbRow, nmbCols).Text
        strFullPath = strFolder & strA1 & "." & strB1 & strFT
        strHPL = "=hyperlink(" & """" & strFullPath & """,""" & strA1 & """)"
        strHPLO = "=hyperlink(" & """" & strFullPath & """,""" & strB1 & """)"

        Cells(r, "J").Formula = strHPL
        Cells(r, "J").Offset(nmbRow, nmbCols).Formula = strHPLO
    Next r
   
End Sub
 
Upvote 0
Solution
It is now throwing a "Compile error: Invalid outside procedure" and highlighting the 0 in the "nmbrow = 0"


Thank you for all of your help Joe! I have so much to learn :/
 
Upvote 0
WordIII said:
It is now throwing a "Compile error: Invalid outside procedure" and highlighting the 0 in the "nmbrow = 0"
Click to expand...
I do not get that error. It works for me.

Did you copy & paste my code, exactly as-is, or did you manually type it or make any edits to it?
Do you have any other VBA code in your module that may be interfering with this (such as public/global variable declarations)?
 
Upvote 0
I tried it again and it worked. Not sure what I was doing the first time. AGAIN THANK YOU so much, have a great day!
 
Upvote 0
You are welcome!
Glad I was able to help.
:)
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Macro: Copy/rename sheet and create hyperlink. Error. Are shared documents not macro friendly?
Replies
1
Views
195
Micron
M
P
  • Question
Applying conditional formatting to merged cells
Replies
1
Views
300
Joe4
Joe4
S
  • Solved
For each row, create new folder based on single cell value, then save exported XML doc to matching folder.
Replies
3
Views
167
shand
S
J
  • Solved
VBA Excel macro to apply a unique color to end of each column (to the end of values, not entire column) only if header name is present and unique
2
Replies
12
Views
1K
JakeP
J
R
  • Solved
SUM of cells that have conditional formatting applied to them
Replies
1
Views
361
Joe4
Joe4

Forum statistics

Threads
1,214,935
Messages
6,122,337
Members
449,078
Latest member
skydd

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