Mocro trouble - Sum if and check for formula
 

I am tring to create a macro that will fill in a shread sheet with based on
the information in antoher workbook.

Here is what i am trying to do:

Get a file to 'read' - (file = Application.GetOpenFilename)

If the cell in the current book contains a formuls do nothing

If not then do the equivelent of this formula:
=IF($C8"",SUMIF([file]Sheet1!$D:$D, $C8, [file]Sheet1!$E:$E),"")

move down one cell and repeat

do until the end of the current column.

when it goes down each row the SUMIF would need to increment C9 then C10 etc
etc

Any help will be much appriciated.

Cheers

Tom

Mocro trouble - Sum if and check for formula
 

ok, i'll bite.........
something like this UNTESTED pseudocode:

sub TomK()
dim Myws as worksheet
my Myrng as range
dim Myrow as long
dim Endrow as long

Application.GetOpenFilename "blah blah"
set Myws =activesheet
set myrng = whatever cell you want to look @
set Myrow = myrng.row
set endrow = cells(5000,1).end(xlup).row

do until myrow = endrow
set myrng = "a" & myrow
if myrng = "" then
'do nothing
else
myrng = " =IF($C" & myrow & """"",SUMIF([file]Sheet1!$D:$D, $C" &
myrow ", _
[file]Sheet1!$E:$E),"""")"
end if
set myrng = "a" & myrow +1
loop

end sub

this hopefully will get you started!
susan


On Mar 9, 7:56 am, TomK wrote:
I am tring to create a macro that will fill in a shread sheet with based on
the information in antoher workbook.

Here is what i am trying to do:

Get a file to 'read' - (file = Application.GetOpenFilename)

If the cell in the current book contains a formuls do nothing

If not then do the equivelent of this formula:
=IF($C8"",SUMIF([file]Sheet1!$D:$D, $C8, [file]Sheet1!$E:$E),"")

move down one cell and repeat

do until the end of the current column.

when it goes down each row the SUMIF would need to increment C9 then C10 etc
etc

Any help will be much appriciated.

Cheers

Tom