Drag Path Down Changing Part of the Path (VBA?)

[Code Ghost]

Good morning community,

I am retrieving seven values from a saved Invoice to a different Workbook.
I have seven rows, from an invoice, to retrieve the values to columns Q to W in a different workbook.


My paths start on row three as follows (not the complete path but the important elements are at the end):

Column .... Q .... R .... S .... T .... U .... V .... W
Row 3 (path) .. ='C:\Users\[1000.xlsx]Invoice'!AI38 .... [1000.xlsx]Invoice'!AI39 .... AI40 .... AI41 .... AI42 .... AI42 .... AI44
Row 4 ...... ='C:\Users\[1001.xlsx]Invoice'!AI38 .... [1001.xlsx]Invoice'!AI39 .... AI40 .... AI41 .... AI42 .... AI42 .... AI44
Row 5 ...... ='C:\Users\[1002.xlsx]Invoice'!AI38 .... [1002.xlsx]Invoice'!AI39 .... AI40 .... AI41 .... AI42 .... AI42 .... AI44

So, I need to drag the path down as it changes the invoice number by row but leaves the cell number in the column as is.

But dragging down the path in Excel changes only the cell (which I don't want to touch) and doesn't change the invoice number (which I want to change to the next invoice number) (Please see the images because once posted).

If I had to modify the path to 100 rows I would do it manually, but I need to do this for 1000 or more rows.
I think VBA would help, but I can't figure out how.
If it is of any use, the invoice number is also stored in column A.

I truly appreciate in anticipation all the help and advice given.

 

[Micron]

Why don't you break the elements into columns? Then if you need the elements to all be in one cell, use a formula for that column. Like
Column (change to suit):
A 'C:\Users\[
B: 1001 <<dragging this down should increment the number
C: .xlsx]Invoice'!A
D: I38 I think this is 138 and not I38? If it's "I" then put the letter with A in column C. Drag down the number as above.
Q1: = A1 & B1 & C1 & D1

 

[Code Ghost]

Why don't you break the elements into columns? Then if you need the elements to all be in one cell, use a formula for that column. Like
Column (change to suit):
A 'C:\Users\[
B: 1001 <<dragging this down should increment the number
C: .xlsx]Invoice'!A
D: I38 I think this is 138 and not I38? If it's "I" then put the letter with A in column C. Drag down the number as above.
Q1: = A1 & B1 & C1 & D1

Hi Micron!

Thanks again for your help!

The path copies a partial payment from an invoice saved in a different folder.
As far as I know, breaking down the path won't retrieve anything.

Do you think there's a way in VBA to isolate the invoice number within the path and increase it by one in the next row down on my seven columns?

Something like (please don't laugh)

Dim inv As Range
Dim invno As ( the middle part where the invoice is ["1000.xlsx]")
inv = Range("Q3:W3")
Set nextrec = Sheet3.Range("A10000").End(xlUp).Offset(1, 0)
nextrec = invno + 1

I know I'm pathetic, but I do appreciate the good will of this community.
Thank you Micron!

 

[Micron]

As far as I know, breaking down the path won't retrieve anything.

if you need the elements to all be in one cell, use a formula

Q1: = A1 & B1 & C1 & D1

So Q1 is the complete path.
What you're now suggesting should be doable but overly complicated if the first suggestion works, and I can't see why not. However, it's looking like you'd need what, 30 columns for breaking out the path into elemental parts?

Using code, you'd want to isolate only 1000 from this: 'C:\Users\[1000.xlsx]Invoice'!AI38 and replace it with 1001? Then maybe not so bad. IF the number is always after the first [ then you find that position (if the leading quote must remain, that would be 11) and add 1. Then you find the first dot (16) and subtract 11 from it, giving you 5 - the length of your number. Then add 1 to that number = 1001. What's next I'm not sure. If it's OK to modify the spreadsheet entry to 1001 then use Replace function and swap 1000 with 1001. If you can't alter the current values then you need a way to create and store a seed value for the next time, otherwise you start the process over again with 1000.

I'm still not clear on how many columns you'd need to break it out. Maybe just 3 and let the drag down alter the row values if that is what you want. If it's not, then use $A$38 or A$38, not A38.

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Drag Path Down Changing Part of the Path
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Code Ghost]

So Q1 is the complete path.
What you're now suggesting should be doable but overly complicated if the first suggestion works, and I can't see why not. However, it's looking like you'd need what, 30 columns for breaking out the path into elemental parts?

Using code, you'd want to isolate only 1000 from this: 'C:\Users\[1000.xlsx]Invoice'!AI38 and replace it with 1001? Then maybe not so bad. IF the number is always after the first [ then you find that position (if the leading quote must remain, that would be 11) and add 1. Then you find the first dot (16) and subtract 11 from it, giving you 5 - the length of your number. Then add 1 to that number = 1001. What's next I'm not sure. If it's OK to modify the spreadsheet entry to 1001 then use Replace function and swap 1000 with 1001. If you can't alter the current values then you need a way to create and store a seed value for the next time, otherwise you start the process over again with 1000.

I'm still not clear on how many columns you'd need to break it out. Maybe just 3 and let the drag down alter the row values if that is what you want. If it's not, then use $A$38 or A$38, not A38.

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Drag Path Down Changing Part of the Path
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Thanks, Fluff! Will do!

 

[Code Ghost]

So Q1 is the complete path.
What you're now suggesting should be doable but overly complicated if the first suggestion works, and I can't see why not. However, it's looking like you'd need what, 30 columns for breaking out the path into elemental parts?

Using code, you'd want to isolate only 1000 from this: 'C:\Users\[1000.xlsx]Invoice'!AI38 and replace it with 1001? Then maybe not so bad. IF the number is always after the first [ then you find that position (if the leading quote must remain, that would be 11) and add 1. Then you find the first dot (16) and subtract 11 from it, giving you 5 - the length of your number. Then add 1 to that number = 1001. What's next I'm not sure. If it's OK to modify the spreadsheet entry to 1001 then use Replace function and swap 1000 with 1001. If you can't alter the current values then you need a way to create and store a seed value for the next time, otherwise you start the process over again with 1000.

I'm still not clear on how many columns you'd need to break it out. Maybe just 3 and let the drag down alter the row values if that is what you want. If it's not, then use $A$38 or A$38, not A38.

Hi Micron!

Thank you for your answers and time.
I have decided to go in a different direction.
I will just reference one single cell with the calculated balance instead of seven cells with partial payments.

Thank you so so much!