Inserting formula dynamically

[kevin_moo]

Hi,
It's possible to get the value partially from the filename and feed the formula multiple times.
Basically, I have tons of files like this: XXXX_1953_GGGGG.xlsx XXXX_1110_GGGGG.xlsx about 78 files - same location format.

Would like to extract and insert the below code.


This will work in below but will require manually enter 78 times.

 Sub test()
Dim  i As Long
Dim vVal As Variant
vVal = Array("Kan","Tik","Big")  ' etc...
For i = 1 To UBound(vVal)
wb.Sheets("Sheet1").Range("Y" & i ).Value = Application.SumIfs(Sheet1.Columns(2), Sheet1.Columns(1), "1953", Sheet1.Columns(8), vVal(i))
next i
End Sub

Thanks in advance

[vba_php]

extract WHAT from each book? and, to correct your code and mirror it directly from what you posted, it SHOULD technically be this (without inserting the code needed to extract what you're wanting to, from the books):

 Sub test()
Dim  i As Long
Dim vVal As variant
vVal = Array("Kan","Tik","Big")  ' etc...
For i = 0 To UBound(vVal)
wb.Sheets("Sheet1").Range("Y" & i ).Value = Application.SumIfs(Sheet1.Columns(2), Sheet1.Columns(1), "1953", Sheet1.Columns(8), vVal(i))
next i
End Sub

I did not look up the specifics of the SUMIFS() function, and whether you have the right data types in the arguments. however, array lower bounds have to start at ''0'' to get all the elements out of them in a loop. the only time they would start at ''1'' is if you had this code above all of the other code in any give module:

option base 1

[kevin_moo]

Thanks Adam.

From each book - we need to extract a partial file name in this format ( always)

C:\ XXXX_1953_GGGGG.xlsx ( extraxt '1953")
C:\ XXXX_101_GGGGG.xlsx (extraxt"101")

....cont!

manually we can do this for each of the 78 files:

  
Sub Combined()
wb.Sheets("Sheet1").Range("Y1").Value = Application.SumIfs(Sheet1.Columns(2), Sheet1.Columns(1), "1953", Sheet1.Columns(8), "Kan")
wb.Sheets("Sheet1").Range("Y2").Value = Application.SumIfs(Sheet1.Columns(2), Sheet1.Columns(1), "1953", Sheet1.Columns(8), "Tik")
wb.Sheets("Sheet1").Range("Y3").Value = Application.SumIfs(Sheet1.Columns(2), Sheet1.Columns(1), "1953", Sheet1.Columns(8), "Big")
End Sub


Sub Combined1()
wb.Sheets("Sheet1").Range("Y4").Value = Application.SumIfs(Sheet1.Columns(2), Sheet1.Columns(1), "101", Sheet1.Columns(8), "Kan")
wb.Sheets("Sheet1").Range("Y5").Value = Application.SumIfs(Sheet1.Columns(2), Sheet1.Columns(1), "101", Sheet1.Columns(8), "Tik")
wb.Sheets("Sheet1").Range("Y6").Value = Application.SumIfs(Sheet1.Columns(2), Sheet1.Columns(1), "101", Sheet1.Columns(8), "Big")
End Sub

Ideally would like to open each workbook (same folder)

Extract the varbiage (i.e "1953)

example:

Const FilePath = "C:\XXXX_1953_GGGGG.xlsx"

Dim MidStart As Long
MidStart = InStrRev(FilePath, "\") + 6

Dim MidEnd As Long
MidEnd = InStrRev(FilePath, "GGGGG")

Dim MyText As String
Mynum = Mid(FilePath, MidStart, MidEnd - MidStart)

.. and insert mynum into the formula..do the second file..etc

output:
w1:mynum (1953) y1: sumif result ( value)
w2:mynum (1953) y2: sumif result ( value)
w1:mynum (1953) y1: sumif result ( value)

Thanks again

[vba_php]

don't really understand this:

Extract the varbiage (i.e "1953)

you mean pull the numbers out of the file name as a string? as indicated in your example below?

example:

Const FilePath = "C:\XXXX_1953_GGGGG.xlsx"

Dim MidStart As Long
MidStart = InStrRev(FilePath, "\") + 6

Dim MidEnd As Long
MidEnd = InStrRev(FilePath, "GGGGG")

Dim MyText As String
Mynum = Mid(FilePath, MidStart, MidEnd - MidStart)

but not understanding this:

.. and insert mynum into the formula..do the second file..etc

output:
w1:mynum (1953) y1: sumif result ( value)
w2:mynum (1953) y2: sumif result ( value)
w1:mynum (1953) y1: sumif result ( value)

yes, you can extract the numbers out of a file name. you can use string functions to do it, as you have already demonstrated that you know how to do. and the way you do it, without using complex algorithmic code like REGEX(), is:

mid(filepath, instrrev(filepath, "\") + 1)

furthermore, constants can be used to get string variables assigned anything, even though constants and variables are 2 different things.

[kevin_moo]

Thanks Adam,
Yes - pull the numbers out of the file name as a string and feed my sumif formula .

I can manually enter each files string and run what I have now - but the problem is the folder has 78 other files with a similar format.

w1:mynum (1953) y1: sumif result ( value)
w2:mynum (1953) y2: sumif result ( value)
w1:mynum (1953) y1: sumif result ( value)

at the end - I want put ( string) in cell w1 and put my sumif result VALUE in y1 - do next .
EXAMPLE MANUAL:

Sub SumIf1()
Worksheets("Sheet1").Activate
Range("B" & rows.Count).End(xlUp).Offset(1, 0).Value = _
 Application.SumIfs(Sheet1.Columns(2), Sheet1.Columns(1), "1953", Sheet1.Columns(8), "Kan")
End Sub

Sub new1()
Dim rows As Long
Dim wb As Workbook
Set wb = ActiveWorkbook
With wb.Sheets("Sheet1")
lrow = .Range("B" & .rows.Count).End(xlUp).Row
.Range("B" & lrow, "b" & lrow).Copy
wb.Sheets("Sheet1").Range("y1").PasteSpecial xlPasteAll
End With

Hope it's clear now.. If It's its too comlicated or not doable - no worries.

Thanks again

[vba_php]

i'm not really following unfortunately, but I can probably better understand if you uploaded a workbook in which you are trying to run the code inside. got that?

[kevin_moo]

Thanks, Adam,
I have attached two sample workbook image ( file 1 & File 2);

Desired result:

10045 210 ( sum col e)
10045 14 (sum col e)

4567 130 ( sum col e)
4567 70 ( sum col e)

File 2.JPG
file 1.JPG


Thanks Again

[vba_php]

I see no correlation whatsoever between ur file name and the additions you are performing on the numbers. for instance, the 1st name is:
[quote[
10045
[/quote]
but yet you want 210 and 14 in the addition formula?

and in the image, the numbers are: 200, 5 and 5 in column ''E''. I'm sure you see why I'm confused?

[kevin_moo]

In will say sorry the confusion - but I don't see the descrepencies yet.

We have two files ; first file : "Filetwo_10045_word. xlsx" and has only two columns (E and S) - and if you sum iondividual columns you will get 210 & 14 . Now pull the string from the file name and map sum result - in this example "10045"

so you will have :

10045 210 - Col A and Col B
10045 14 - Col A and Col B

Will do the same next file and so an

Thanks

[vba_php]

i'm sorry Kevin, but I'm not following. I think i'll have to drop out and I dearly apologize if I wasted time here. I hope someone else can jump in.

[kevin_moo]

Thanks Adam - no worries.
I think I figure out.

Thanks anyway.