Help automating updating columns (find and replace) with macros.

S

Stacker

Board Regular
Joined
Jul 11, 2021
Messages
87
Office Version
  1. 365
Platform
  1. Windows
I routinely get files and people here have helped me with automating one component. However I wanna create a macros that allows m to automate the updating of certain worksheets. I am given an excel file with three tabs.

In the first tab i get a lot of hospital data and one of my tasks is to to copy the last column say MG and paste it into the MH. All of the cells in the column follow a certain formula. The top being like so:

=SUM('\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[270821 HRI.xlsx]Daily sitrep'!$F$8:$F$11)

I have to paste this column into the next and from there ctrl and f and find and replace. So I would find and replace 270821 with 280821 solely in that column.

Is there a way to copy and paste the previous column and then find and replace by incrememnts of 1 day using a macro.

The second and third stuff are fairly quick so I am not bothered
 
Sort by date Sort by votes
Thanks regardless. I wouldn't have thought it would be that complex haha.
 
Upvote 0

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
You're welcome and thanks for the feedback.
 
Upvote 0
I was wondering if anyone else has any ideas as to what can be done?
 
Upvote 0
GWteB said:
It may be wise to move away from the current concept and collect cumulative data in a different way.
Click to expand...
You might consider getting acquainted with Power Query. That's the right tool for this kind of job.
 
Upvote 0
It's designed for tasks you're now trying to achieve with multiple links to multiple workbooks. You might want to consider watching the video I posted a link to.
 
Upvote 0
I use power query it's just preparing the data in line with the established format would be way too cumbersome. I have thought of a different idea however. I have thought that once i have converted the original file names into the desired file naming convention. I can then just create a new file with the desired formulas in the appropriate cells and then just copy and paste the formulas into the relevant worksheet. However the only problems is when i try to drag the formula it fails as shown in column D. So as you can see below cell A3 has a formula with 210821 HRI inside it, and cell b2 should have the same formula but instead 220821 HRI. I can convert the date to the desired format, however, i can't crack how to refer to that number all the way down the column. Any tips. Here is how the dummy table looks

Test Excel.xlsx
ABCD
1210821220821230821240821
221/08/202122/08/202123/08/202124/08/2021
314141314
400014
555414
611114
700014
800014
922214
1011014
1100014
1210114
1332232333114
1412121214
1500014
1630214
Sample
Cell Formulas
RangeFormula
A1:D1A1=A2
A3,D3:D16A3=SUM('\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[210821 HRI.xlsx]Daily sitrep'!$F$8:$F$11)
B3B3=SUM('\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[220821 HRI.xlsx]Daily sitrep'!$F$8:$F$11)
C3C3=SUM('\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[230821 HRI.xlsx]Daily sitrep'!$F$8:$F$11)
A4A4=SUM('\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[210821 HRI.xlsx]Daily sitrep'!$G$8:$G$11)
B4B4=SUM('\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[220821 HRI.xlsx]Daily sitrep'!$G$8:$G$11)
C4C4=SUM('\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[230821 HRI.xlsx]Daily sitrep'!$G$8:$G$11)
A5A5=SUM('\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[210821 HRI.xlsx]Daily sitrep'!$F$8:$I$8)
B5B5=SUM('\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[220821 HRI.xlsx]Daily sitrep'!$F$8:$I$8)
C5C5=SUM('\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[230821 HRI.xlsx]Daily sitrep'!$F$8:$I$8)
A6A6='\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[210821 HRI.xlsx]Daily sitrep'!$F$8
B6B6='\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[220821 HRI.xlsx]Daily sitrep'!$F$8
C6C6='\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[230821 HRI.xlsx]Daily sitrep'!$F$8
A7A7='\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[210821 HRI.xlsx]Daily sitrep'!$G$8
B7B7='\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[220821 HRI.xlsx]Daily sitrep'!$G$8
C7C7='\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[230821 HRI.xlsx]Daily sitrep'!$G$8
A8A8='\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[210821 HRI.xlsx]Daily sitrep'!$I$8
B8B8='\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[220821 HRI.xlsx]Daily sitrep'!$I$8
C8C8='\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[230821 HRI.xlsx]Daily sitrep'!$I$8
A9A9=SUM('\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[210821 HRI.xlsx]Daily sitrep'!$F$9:$I$9)
B9B9=SUM('\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[220821 HRI.xlsx]Daily sitrep'!$F$9:$I$9)
C9C9=SUM('\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[230821 HRI.xlsx]Daily sitrep'!$F$9:$I$9)
A10A10='\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[210821 HRI.xlsx]Daily sitrep'!$F$9
B10B10='\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[220821 HRI.xlsx]Daily sitrep'!$F$9
C10C10='\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[230821 HRI.xlsx]Daily sitrep'!$F$9
A11A11='\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[210821 HRI.xlsx]Daily sitrep'!$G$9
B11B11='\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[220821 HRI.xlsx]Daily sitrep'!$G$9
C11C11='\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[230821 HRI.xlsx]Daily sitrep'!$G$9
A12A12='\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[210821 HRI.xlsx]Daily sitrep'!$I$9
B12B12='\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[220821 HRI.xlsx]Daily sitrep'!$I$9
C12C12='\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[230821 HRI.xlsx]Daily sitrep'!$I$9
A13A13=SUM('\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[210821 HRI.xlsx]Daily sitrep'!$F$10:$I$10)
B13B13=SUM('\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[220821 HRI.xlsx]Daily sitrep'!$F$10:$I$10)
C13C13=SUM('\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[230821 HRI.xlsx]Daily sitrep'!$F$10:$I$10)
A14A14='\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[210821 HRI.xlsx]Daily sitrep'!$F$10
B14B14='\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[220821 HRI.xlsx]Daily sitrep'!$F$10
C14C14='\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[230821 HRI.xlsx]Daily sitrep'!$F$10
A15A15='\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[210821 HRI.xlsx]Daily sitrep'!$G$10
B15B15='\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[220821 HRI.xlsx]Daily sitrep'!$G$10
C15C15='\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[230821 HRI.xlsx]Daily sitrep'!$G$10
A16A16='\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[210821 HRI.xlsx]Daily sitrep'!$I$10
B16B16='\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[220821 HRI.xlsx]Daily sitrep'!$I$10
C16C16='\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[230821 HRI.xlsx]Daily sitrep'!$I$10
 
Upvote 0
Solution
Perhaps
Rich (BB code): 
=SUM('\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[" & TEXT(A$1;"ddmmjj") & " HRI.xlsx]Daily sitrep'!$F$8:$F$11)



Book1
ABCD
121-8-202122-8-202123-8-202124-8-2021
2#REF!#REF!#REF!#REF!
3#REF!#REF!#REF!#REF!
4#REF!#REF!#REF!#REF!
Sheet1
Cell Formulas
RangeFormula
A2:D4A2=SUM('\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[" & TEXT(A$1;"ddmmyy") & " HRI.xlsx]Daily sitrep'!$F$8:$F$11)
 
Upvote 0
This formula failed. Any idea what's wrong?

=SUM('\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[[" & TEXT(D$2;"ddmmyy") & " HRI.xlsx]Daily sitrep'!$F$8:$F$11)
 
Upvote 0
It looks like we both have similar regional settings (semicolon being the list separator character) so in that regard it should work.
Ah, did not notice it before, but the XL2BB tool translates square brackets to text on this board (eg. lsqb & rsqb) so remove the &lsqb part after Both Trusts\[ and it should work.
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question
search in cell below previously found cell in VBA
Replies
1
Views
366
ChrisFoster
C
C
  • Question
Find and Replace Macro for Excel (If x exists then replace y with x) - For Microsoft Excel
Replies
1
Views
433
HaHoBe
HaHoBe
H
  • Solved
Replace cells in column B based on column A
Replies
5
Views
514
hotpinkleg
H
S
  • Question
VBA Code- finding #Spill and moving the formula to the next column
Replies
0
Views
412
stromnoexcel
S
Ottsel
  • Solved
VBA: Locate a Column, then Dim it Using Find or Alternative Method
Replies
6
Views
329
Ottsel
Ottsel

Forum statistics

Threads
1,215,024
Messages
6,122,729
Members
449,093
Latest member
Mnur

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