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

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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
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

Y
  • Question Question
Macro to find & replace from source table - need to match whole source table string to partial string in workbook
Replies
1
Views
404
Kaper
Kaper
W
  • Question Question
Macro/VBA code code for creating new excel file for each pivot table from a column filter
Replies
1
Views
442
Anthony47
A
L
  • Question Question
search in cell below previously found cell in VBA
Replies
1
Views
505
ChrisFoster
C
C
  • Question Question
Find and Replace Macro for Excel (If x exists then replace y with x) - For Microsoft Excel
Replies
1
Views
575
HaHoBe
HaHoBe
H
  • Solved
Replace cells in column B based on column A
Replies
5
Views
916
hotpinkleg
H

Forum statistics

Threads
1,223,911
Messages
6,175,323
Members
452,635
Latest member
laura12345

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