catchacold
02-05-2009, 07:26 AM
This is the first issue I have to tackle!
OKay I dont know really anything about VB - But, we have this crazy Excel work book that creates another workbook with formulas in it - (That is the very short version). The problem is when the new workbook is created it is doubling the value in G6
I think the problem is in asFormula2 - But I dont know how to create it
TIA!!!!
_________________________________________________________
'Proposed Change Order
asFormula = "="
asFormula2 = "="
For Each s In Worksheets
s.Select
If s.Name <> "Data" And s.Name <> "Summary" Then
For ai1to37 = 1 To 9999
If InStr(1, Sheets(s.Name).Cells(ai1to37, 1).Value, "TOTALS FOR DIVISION") Then
ai1to37 = ai1to37 - 3
Exit For
End If
Next
If Len(asFormula) > 500 Then
asFormula2 = asFormula2 & " + SUMIF(" & s.Name & "!C4:" & s.Name & "!C" & ai1to37 & "," & """LA""" & "," & s.Name & "!G4:" & s.Name & "!G" & ai1to37 & ")"
Else
asFormula = asFormula & " + SUMIF(" & s.Name & "!C4:" & s.Name & "!C" & ai1to37 & "," & """LA""" & "," & s.Name & "!G4:" & s.Name & "!G" & ai1to37 & ")"
End If
End If
Next
'LA Totals
Sheets(asSheetName).Cells(aiTargetRow, 38).Value = asFormula
Sheets(asSheetName).Cells(aiTargetRow, 39).Value = IIf(asFormula2 = "=", 0, asFormula2)
Sheets(asSheetName).Cells(aiTargetRow, 7).Value = "=AL2 + AM2"
asFormula = "="
asFormula2 = "="
For Each s In Worksheets
s.Select
If s.Name <> "Data" And s.Name <> "Summary" Then
For ai1to37 = 1 To 9999
If InStr(1, Sheets(s.Name).Cells(ai1to37, 1).Value, "TOTALS FOR DIVISION") Then
ai1to37 = ai1to37 - 3
Exit For
End If
Next
If Len(asFormula) > 500 Then
asFormula2 = asFormula2 & " + SUMIF(" & s.Name & "!C4:" & s.Name & "!C" & ai1to37 & "," & """MA""" & "," & s.Name & "!G4:" & s.Name & "!G" & ai1to37 & ")"
Else
asFormula = asFormula & " + SUMIF(" & s.Name & "!C4:" & s.Name & "!C" & ai1to37 & "," & """MA""" & "," & s.Name & "!G4:" & s.Name & "!G" & ai1to37 & ")"
End If
End If
Next
Edit Lucas: VBA tags added to code. You can select your code when posting and hit the vba button to format it for the forum.
Adonaioc
02-05-2009, 08:20 AM
Can you post the workbook?
catchacold
02-05-2009, 08:31 AM
I cant cause it pulls against out SQL db and that has our pw and username stuff in the VB
Can you post the workbook?
catchacold
02-05-2009, 08:34 AM
Let me explain some more - On sheet 39 G5 you can input a value - That value carries over to a sheet called summary - In the summary sheet I25 is were it doubles - I25 also pulls from other cells - Its crazy -
lucas
02-05-2009, 08:36 AM
If you can tell us what the formula should look like in a cell maybe someone can help you format the code to put the correct formula in the cell.......
catchacold
02-05-2009, 08:40 AM
Thats the thing the formula in the cell is correct (I had to go back and edit my first post) - What it does on sheet 39 is G6 =SUM(G5:G5)? Thats correct. But what ever value you put in there it doubles on Summary page I25. I have added all the forumlas up manually and they are correct. It has to do with something in the workbook that creates this workbook that I am talking about right now. haha - And I think it has to do with the VB from the following
'Proposed Change Order
asFormula = "="
asFormula2 = "="
For Each s In Worksheets
s.Select
If s.Name <> "Data" And s.Name <> "Summary" Then
For ai1to37 = 1 To 9999
If InStr(1, Sheets(s.Name).Cells(ai1to37, 1).Value, "TOTALS FOR DIVISION") Then
ai1to37 = ai1to37 - 3
Exit For
End If
Next
If Len(asFormula) > 500 Then
asFormula2 = asFormula2 & " + SUMIF(" & s.Name & "!C4:" & s.Name & "!C" & ai1to37 & "," & """LA""" & "," & s.Name & "!G4:" & s.Name & "!G" & ai1to37 & ")"
Else
asFormula = asFormula & " + SUMIF(" & s.Name & "!C4:" & s.Name & "!C" & ai1to37 & "," & """LA""" & "," & s.Name & "!G4:" & s.Name & "!G" & ai1to37 & ")"
End If
End If
Next
'LA Totals
Sheets(asSheetName).Cells(aiTargetRow, 38).Value = asFormula
Sheets(asSheetName).Cells(aiTargetRow, 39).Value = IIf(asFormula2 = "=", 0, asFormula2)
Sheets(asSheetName).Cells(aiTargetRow, 7).Value = "=AL2 + AM2"
asFormula = "="
asFormula2 = "="
For Each s In Worksheets
s.Select
If s.Name <> "Data" And s.Name <> "Summary" Then
For ai1to37 = 1 To 9999
If InStr(1, Sheets(s.Name).Cells(ai1to37, 1).Value, "TOTALS FOR DIVISION") Then
ai1to37 = ai1to37 - 3
Exit For
End If
Next
If Len(asFormula) > 500 Then
asFormula2 = asFormula2 & " + SUMIF(" & s.Name & "!C4:" & s.Name & "!C" & ai1to37 & "," & """MA""" & "," & s.Name & "!G4:" & s.Name & "!G" & ai1to37 & ")"
Else
asFormula = asFormula & " + SUMIF(" & s.Name & "!C4:" & s.Name & "!C" & ai1to37 & "," & """MA""" & "," & s.Name & "!G4:" & s.Name & "!G" & ai1to37 & ")"
End If
End If
Next
'MA Totals
Sheets(asSheetName).Cells(aiTargetRow + 1, 38).Value = asFormula
Sheets(asSheetName).Cells(aiTargetRow + 1, 39).Value = IIf(asFormula2 = "=", 0, asFormula2)
Sheets(asSheetName).Cells(aiTargetRow + 1, 7).Value = "=AL3 + AM3"
asFormula = "="
asFormula2 = "="
For Each s In Worksheets
s.Select
If s.Name <> "Data" And s.Name <> "Summary" Then
For ai1to37 = 1 To 9999
If InStr(1, Sheets(s.Name).Cells(ai1to37, 1).Value, "TOTALS FOR DIVISION") Then
ai1to37 = ai1to37 - 3
Exit For
End If
Next
If Len(asFormula) > 500 Then
asFormula2 = asFormula2 & " + SUMIF(" & s.Name & "!C4:" & s.Name & "!C" & ai1to37 & "," & """SU""" & "," & s.Name & "!G4:" & s.Name & "!G" & ai1to37 & ")"
Else
asFormula = asFormula & " + SUMIF(" & s.Name & "!C4:" & s.Name & "!C" & ai1to37 & "," & """SU""" & "," & s.Name & "!G4:" & s.Name & "!G" & ai1to37 & ")"
End If
End If
Next
'SU Totals
Sheets(asSheetName).Cells(aiTargetRow + 2, 38).Value = asFormula
Sheets(asSheetName).Cells(aiTargetRow + 2, 39).Value = IIf(asFormula2 = "=", 0, asFormula2)
Sheets(asSheetName).Cells(aiTargetRow + 2, 7).Value = "=AL4 + AM4"
Sheets(asSheetName).Cells(aiTargetRow + 3, 7).Value = "=G" & aiTargetRow & "+G" & aiTargetRow + 1 & "+G" & aiTargetRow + 2
'Forecast
asFormula = "="
asFormula2 = "="
For Each s In Worksheets
s.Select
If s.Name <> "Data" And s.Name <> "Summary" Then
For ai1to37 = 1 To 9999
If InStr(1, Sheets(s.Name).Cells(ai1to37, 1).Value, "TOTALS FOR DIVISION") Then
ai1to37 = ai1to37 - 3
Exit For
End If
Next
If Len(asFormula) > 500 Then
asFormula2 = asFormula2 & " + SUMIF(" & s.Name & "!C4:" & s.Name & "!C" & ai1to37 & "," & """LA""" & "," & s.Name & "!I4:" & s.Name & "!I" & ai1to37 & ")"
Else
asFormula = asFormula & " + SUMIF(" & s.Name & "!C4:" & s.Name & "!C" & ai1to37 & "," & """LA""" & "," & s.Name & "!I4:" & s.Name & "!I" & ai1to37 & ")"
End If
End If
Next
'LA Totals
Sheets(asSheetName).Cells(aiTargetRow, 30).Value = asFormula
Sheets(asSheetName).Cells(aiTargetRow, 31).Value = IIf(asFormula2 = "=", 0, asFormula2)
Sheets(asSheetName).Cells(aiTargetRow, 9).Value = "=AD2 + AE2"
asFormula = "="
asFormula2 = "="
For Each s In Worksheets
s.Select
If s.Name <> "Data" And s.Name <> "Summary" Then
For ai1to37 = 1 To 9999
If InStr(1, Sheets(s.Name).Cells(ai1to37, 1).Value, "TOTALS FOR DIVISION") Then
ai1to37 = ai1to37 - 3
Exit For
End If
Next
If Len(asFormula) > 500 Then
asFormula2 = asFormula2 & " + SUMIF(" & s.Name & "!C4:" & s.Name & "!C" & ai1to37 & "," & """MA""" & "," & s.Name & "!I4:" & s.Name & "!I" & ai1to37 & ")"
Else
asFormula = asFormula & " + SUMIF(" & s.Name & "!C4:" & s.Name & "!C" & ai1to37 & "," & """MA""" & "," & s.Name & "!I4:" & s.Name & "!I" & ai1to37 & ")"
End If
End If
Next
'MA Totals
Sheets(asSheetName).Cells(aiTargetRow + 1, 30).Value = asFormula
Sheets(asSheetName).Cells(aiTargetRow + 1, 31).Value = IIf(asFormula2 = "=", 0, asFormula2)
Sheets(asSheetName).Cells(aiTargetRow + 1, 9).Value = "=AD3 + AE3"
asFormula = "="
asFormula2 = "="
For Each s In Worksheets
s.Select
If s.Name <> "Data" And s.Name <> "Summary" Then
For ai1to37 = 1 To 9999
If InStr(1, Sheets(s.Name).Cells(ai1to37, 1).Value, "TOTALS FOR DIVISION") Then
ai1to37 = ai1to37 - 3
Exit For
End If
Next
If Len(asFormula) > 500 Then
asFormula2 = asFormula2 & " + SUMIF(" & s.Name & "!C4:" & s.Name & "!C" & ai1to37 & "," & """SU""" & "," & s.Name & "!I4:" & s.Name & "!I" & ai1to37 & ")"
Else
asFormula = asFormula & " + SUMIF(" & s.Name & "!C4:" & s.Name & "!C" & ai1to37 & "," & """SU""" & "," & s.Name & "!I4:" & s.Name & "!I" & ai1to37 & ")"
End If
End If
Next
'SU Totals
Sheets(asSheetName).Cells(aiTargetRow + 2, 30).Value = asFormula
Sheets(asSheetName).Cells(aiTargetRow + 2, 31).Value = IIf(asFormula2 = "=", 0, asFormula2)
Sheets(asSheetName).Cells(aiTargetRow + 2, 9).Value = "=AD4 + AE4"
Sheets(asSheetName).Cells(aiTargetRow + 3, 9).Value = "=I" & aiTargetRow & "+I" & aiTargetRow + 1 & "+I" & aiTargetRow + 2
If you can tell us what the formula should look like in a cell maybe someone can help you format the code to put the correct formula in the cell.......
lucas
02-05-2009, 08:57 AM
edit your thread and select your code and hit the vba button as I did for you in post #1 please.
And I think it has to do with the VB from the following
We understand that vba is creating the formula for you but we need to know what the formula in the cell should look like so we can compare it to the code to see why it's not creating it properly. The formula in sheet summary page 125 if there is one.
Remember, we can't see over your shoulder. We have no idea what you are seeing. You have to be our eyes.
catchacold
02-05-2009, 09:18 AM
I guess the only way to show you is listing what forumulas are in each cell
this is all from the summary page
1 - I25 = h25-i25 (this is were the value from sheet 39 G6 doubles)
2 - H25 = =D25+E25+F25+G25
3 - d25 = =SUMIF(Data!H4:Data!H146,"39",Data!K4:Data!K12)
4 - e25 ='39'!E12
5 - f25 = =SUMIF(Data!H4:Data!H146,"39",Data!L4:Data!L12)
6 - g25 ='39'!G12
Is this what you looking for? If so I can keep breaking it down.
:bug:
edit your thread and select your code and hit the vba button as I did for you in post #1 please.
We understand that vba is creating the formula for you but we need to know what the formula in the cell should look like so we can compare it to the code to see why it's not creating it properly. The formula in sheet summary page 125 if there is one.
Remember, we can't see over your shoulder. We have no idea what you are seeing. You have to be our eyes.
catchacold
02-05-2009, 09:22 AM
Okay if I use the following formula in I25 Summary sheet - It works
=(H25-I21)-('39'!G6)
But, I would like it to be in the VB when the sheet is created
catchacold
02-05-2009, 09:56 AM
I think the easiest thing to do would be create a macro to fix that - The end user could run the macro before using the sheet to fix everything. Hmm- I guess that is a option
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.