PDA

View Full Version : Solved: problems with loop



samohtwerdna
11-10-2005, 06:32 AM
I'm working on a little Addin for excel that calculates the square feet of a job and multiplies it by a finish cost. Our pricing software allows us to export to excel and has a column for quantity - width and height. I can't just multiply the the sum of widths to the sum of heights because of the quantity factor. What should I do??

Oh, and my Addin is a little user form that has a combo box for selecting finish a text box for the finish cost (only three options) and a final cost text box at the bottom.

It's all very simple- but I cant seem to figure out what to do about the quantity. Probably I need to loop through each row multiplying (quant * width * height) and then adding each value to the Next until the ActiveCell IsEmpty.

Can you advise??

mvidas
11-10-2005, 06:45 AM
Hello,

Try the following, just set the columns and you should be all set: Dim CLL As Range, Totl As Double, Qty As Range, Wdth As Range, Hght As Range

Set Qty = Columns("A")
Set Wdth = Columns("B")
Set Hght = Columns("C")

For Each CLL In Intersect(Qty, ActiveSheet.UsedRange)
If IsNumeric(CLL) Then
Totl = Qty + Intersect(CLL.EntireRow, Wdth) + Intersect(CLL.EntireRow, Hght)
End If
Next
MsgBox TotlMatt

samohtwerdna
11-10-2005, 07:06 AM
Hi matt,

Just using your code I get a type missmatch error - because my Totl = 0 in the if statement.

Dim CLL As Range, Totl As Double, Qty As Range, Wdth As Range, Hght As Range

Set Qty = Columns("C")
Set Wdth = Columns("F")
Set Hght = Columns("G")

For Each CLL In Intersect(Qty, ActiveSheet.UsedRange)
If IsNumeric(CLL) Then
Totl = Qty + Intersect(CLL.EntireRow, Wdth) + Intersect(CLL.EntireRow, Hght)
End If
Next
txtQuoteTotal.Value = Totl

am I missing something?

mvidas
11-10-2005, 07:25 AM
D'oh! Stupid typing error on my part, sorry! In the "Totl = " line, change Qty to CLL:' Totl = Qty + Intersect(CLL.EntireRow, Wdth) + Intersect(CLL.EntireRow, Hght)
Totl = CLL + Intersect(CLL.EntireRow, Wdth) + Intersect(CLL.EntireRow, Hght)Matt

samohtwerdna
11-10-2005, 07:44 AM
for some reason I am only getting the Totl for the first row?

mvidas
11-10-2005, 07:49 AM
Ahhh! I should just give up today! VERY sorry! Totl = Totl + CLL + Intersect(CLL.EntireRow, Wdth) + Intersect(CLL.EntireRow, Hght)
It looks like it only would have had the total for the last row rather than the first, but bad code regardless. Again, very sorry!
Matt

samohtwerdna
11-10-2005, 08:02 AM
but of course!!

Thanks for the help! Here is what I have for calulating the square ft.:

Dim CLL As Range, Totl As Double, Qty As Range, Wdth As Range, Hght As Range

Set Qty = Columns("C")
Set Wdth = Columns("F")
Set Hght = Columns("G")

For Each CLL In Intersect(Qty, ActiveSheet.UsedRange)
If IsNumeric(CLL) Then
Totl = Totl + CLL * Intersect(CLL.EntireRow, Wdth) * Intersect(CLL.EntireRow, Hght) / 144
End If
Next

txtQuoteTotal.Value = Totl * txtFinish.Value

Now, I have one more problem. What if I wanted to check for a condition like if Name starts with "WG" then
Where Name is another range and if the condition is true I need to add Qty*((26*Hght)+(108*Wdth)+(1*Wdth*Hght))) or something like it. Because "WG" will have depth as well as width and height to be concidered.

hopefully this makes sense.

Thanks

mvidas
11-10-2005, 08:35 AM
Definately makes sense, give this a try:Dim CLL As Range, Totl As Double, Qty As Range, Wdth As Range, Hght As Range
Dim Nm As Range, Totl2 As Double, vWdth As Double, vHght As Double

Set Qty = Columns("C")
Set Wdth = Columns("F")
Set Hght = Columns("G")
Set Nm = Columns("H")

For Each CLL In Intersect(Qty, ActiveSheet.UsedRange).Cells
If IsNumeric(CLL) Then
vWdth = Intersect(CLL.EntireRow, Wdth).Value
vHght = Intersect(CLL.EntireRow, Hght).Value
Totl = Totl + CLL.Value * vWdth * vHght / 144
If UCase(Left(Intersect(CLL.EntireRow, Nm), 2)) = "WG" Then
Totl2 = Totl2 + CLL.Value * (26 * vHght + 108 * vWdth + vWdth * vHght)
End If
End If
Next

txtQuoteTotal.Value = Totl * txtFinish.ValueMatt

samohtwerdna
11-10-2005, 08:54 AM
Ok this almost works.

When I step into the code and check each of the return values they all come up correctly - but for some reason the end result doesn't treat the "WG" entry properly - or at least is not adding the total that it came up with properly. I get the same Total whether my Nm is "W" or "WG" ??

but still - thanks enormously for the help!:thumb

mvidas
11-10-2005, 08:58 AM
Not sure I'm understanding what you mean.. The code is adding cells that begin with just "W" to the totl2 variables as well as "WG"?

samohtwerdna
11-10-2005, 09:05 AM
That's not the problem. I think the problem is that Totl2 works and calculates fine but doesn't get figured with the grand total. I just realized as I was writing this out that is because:

txtQuoteTotal.Value = Totl * txtFinish.Value So maybe I just need to add Totl1 and Totl2 together first and then multiply that value by my finish value

samohtwerdna
11-10-2005, 09:09 AM
I thought something like this:

Dim CLL As Range, Totl As Double, Qty As Range, Wdth As Range, Hght As Range
Dim Nm As Range, Totl2 As Double, vWdth As Double, vHght As Double, FTotl As Double

Set Qty = Columns("C")
Set Wdth = Columns("F")
Set Hght = Columns("G")
Set Nm = Columns("D")

For Each CLL In Intersect(Qty, ActiveSheet.UsedRange).Cells
If IsNumeric(CLL) Then
vWdth = Intersect(CLL.EntireRow, Wdth).Value
vHght = Intersect(CLL.EntireRow, Hght).Value
Totl = Totl + CLL.Value * vWdth * vHght / 144
If UCase(Left(Intersect(CLL.EntireRow, Nm), 2)) = "WG" Then
Totl2 = Totl2 + CLL.Value * (26 * vHght + 108 * vWdth + vWdth * vHght)
End If
End If
Next

FTotl = Totl + Totl2
txtQuoteTotal.Value = FTotl * txtFinish.Value

But this comes up with a value way to large.

mvidas
11-10-2005, 09:17 AM
As I don't really know what you're trying to accomplish, its hard to say what you would want to do. Would it be possible to attach a sample workbook with just a couple lines of data? That way, you could say what the code is producing, and what you wanted it to produce, and we can figure out how to get to that total.

Of, if Totl and Totl2 are being calculated correctly, you could tell me what your Totl, Totl2, and txtFinish.Value values are, and what you wanted the txtQuoteTotal.Value for those numbers to be. That way you wouldn't have to attach a book.
Matt

samohtwerdna
11-10-2005, 09:25 AM
Ok I'm super dumb! I forgot to divide Totl2 by 144

This gets me very close but for some reason I'm about 400 off in my little tester.

Totl should = 44.5 after being divided and 6408 before
Totl2 should = 37.0 after division and 5334 before

my Finish value is either 40, 50 or 60 - in this case 50 and my grand total should be 4,077.08

I'm getting 4,420.83

does this help?
Thanks,

mvidas
11-10-2005, 09:47 AM
Hmm.. using those numbers I'm also getting 4077.08. Can you post your whole code block again? Or possibly try this (I added a msgbox so you can see the values of totl/totl2/finishvalue before its calculated, also I removed the /144 from the Totl= line and am performing that later on)Dim CLL As Range, Totl As Double, Qty As Range, Wdth As Range, Hght As Range
Dim Nm As Range, Totl2 As Double, vWdth As Double, vHght As Double

Set Qty = Columns("C")
Set Wdth = Columns("F")
Set Hght = Columns("G")
Set Nm = Columns("D")

For Each CLL In Intersect(Qty, ActiveSheet.UsedRange).Cells
If IsNumeric(CLL) Then
vWdth = Intersect(CLL.EntireRow, Wdth).Value
vHght = Intersect(CLL.EntireRow, Hght).Value
Totl = Totl + CLL.Value * vWdth * vHght
If UCase(Left(Intersect(CLL.EntireRow, Nm), 2)) = "WG" Then
Totl2 = Totl2 + CLL.Value * (26 * vHght + 108 * vWdth + vWdth * vHght)
End If
End If
Next

MsgBox "Totl: " & Totl & vbCrLf & "Totl2: " & Totl2 & vbCrLf & "txtFinish: " & txtFinish.Value
txtQuoteTotal.Value = (Totl / 144 + Totl2 / 144) * txtFinish.ValueMatt

samohtwerdna
11-10-2005, 10:20 AM
Matt,

I think the problem is that the Totl is including the "WG" entry instead of skipping it and letting Totl2 caluclate it - If I deleat the Qty of the "WG" entry the Totl = 6408 like it should but if I add back the Qty to 1 then Totl1 = 7398

Here is the whole form thus far:

Option Explicit

Private Sub cboFinish_Change()
If cboFinish.Value = "Crimson" Then
txtFinish.Value = 50
ElseIf cboFinish.Value = "Turquoise" Then
txtFinish.Value = 60
Else
txtFinish.Value = 40
End If

txtFinish.Value = Format(txtFinish.Value, "$##.00")

End Sub

Private Sub cmdQuit_Click()
Unload Me
End Sub

Private Sub cmdQuote_Click()
Dim CLL As Range, Totl As Double, Qty As Range, Wdth As Range, Hght As Range
Dim Nm As Range, Totl2 As Double, vWdth As Double, vHght As Double

Set Qty = Columns("C")
Set Wdth = Columns("F")
Set Hght = Columns("G")
Set Nm = Columns("D")

For Each CLL In Intersect(Qty, ActiveSheet.UsedRange).Cells
If IsNumeric(CLL) Then
vWdth = Intersect(CLL.EntireRow, Wdth).Value
vHght = Intersect(CLL.EntireRow, Hght).Value
Totl = Totl + CLL.Value * vWdth * vHght
If UCase(Left(Intersect(CLL.EntireRow, Nm), 2)) = "WG" Then
Totl2 = Totl2 + CLL.Value * (26 * vHght + 108 * vWdth + vWdth * vHght)
End If
End If
Next

MsgBox "Totl: " & Totl & vbCrLf & "Totl2: " & Totl2 & vbCrLf & "txtFinish: " & txtFinish.Value
txtQuoteTotal.Value = (Totl / 144 + Totl2 / 144) * txtFinish.Value



'Dim CLL As Range, Totl As Double, Qty As Range, Wdth As Range, Hght As Range
'Dim Nm As Range, Totl2 As Double, vWdth As Double, vHght As Double, FTotl As Double
'
'Set Qty = Columns("C")
'Set Wdth = Columns("F")
'Set Hght = Columns("G")
'Set Nm = Columns("D")
'
'For Each CLL In Intersect(Qty, ActiveSheet.UsedRange).Cells
' If IsNumeric(CLL) Then
' vWdth = Intersect(CLL.EntireRow, Wdth).Value
' vHght = Intersect(CLL.EntireRow, Hght).Value
' Totl = Totl + CLL.Value * vWdth * vHght / 144
' If UCase(Left(Intersect(CLL.EntireRow, Nm), 2)) = "WG" Then
' Totl2 = Totl2 + CLL.Value * (26 * vHght + 108 * vWdth + vWdth * vHght) / 144
' End If
' FTotl = Totl + Totl2
' End If
'Next
'
'
'txtQuoteTotal.Value = FTotl * txtFinish.Value
'txtQuoteTotal.Value = Format(txtQuoteTotal.Value, "$###,##0.00")



End Sub

Private Sub UserForm_Initialize()

With cboFinish
.AddItem "Crimson"
.AddItem "Turquoise"
.AddItem "Ivory"
.AddItem "Celedon"
.AddItem "Cream"
.AddItem "Meringue"
.AddItem "Glacier"
End With

End Sub

Not that you need it there is nothing much going on but this function anyway - but just in case...

mvidas
11-10-2005, 10:34 AM
Ohhh, so let me make sure I have it right now :) You don't want the WG entries to be included with Totl? You were right, they were being included. Try this for your "If IsNumeric..." block: If IsNumeric(CLL) Then
vWdth = Intersect(CLL.EntireRow, Wdth).Value
vHght = Intersect(CLL.EntireRow, Hght).Value
If UCase(Left(Intersect(CLL.EntireRow, Nm), 2)) = "WG" Then
Totl2 = Totl2 + CLL.Value * (26 * vHght + 108 * vWdth + vWdth * vHght)
Else
Totl = Totl + CLL.Value * vWdth * vHght
End If
End IfMatt

samohtwerdna
11-10-2005, 11:20 AM
Thanks matt!!

I have one clarifiaction - I actually do want "WG" entries to calculate both the Totl and Totl2 - but I also have a "WO" entry that will only have the Calculation of Totl2 can I do that by just say:

If IsNumeric(CLL) Then
vWdth = Intersect(CLL.EntireRow, Wdth).Value
vHght = Intersect(CLL.EntireRow, Hght).Value
Totl = Totl + CLL.Value * vWdth * vHght
If UCase(Left(Intersect(CLL.EntireRow, Nm), 2)) = "WG" Then
Totl2 = Totl2 + CLL.Value * (26 * vHght + 108 * vWdth + vWdth * vHght)
ElseIf UCase(Left(Intersect(CLL.EntireRow, Nm), 2)) = "WO" Then
' Totl2 = Totl2 + CLL.Value * (26 * vHght + 108 * vWdth + vWdth * vHght)
Totl = Totl + CLL.Value * vWdth * vHght
End If
End If
Okay I know this doesn't work but something like it??

mvidas
11-10-2005, 12:19 PM
OK, again I just want to verify :)

if it starts with "WO" - only include this in the Totl2 calculation
if it starts with "WG" - include in both Totl2 and Totl calculations
Anything else - only Totl calculation

Assuming that is correct, For Each CLL In Intersect(Qty, ActiveSheet.UsedRange).Cells
If IsNumeric(CLL) Then
vWdth = Intersect(CLL.EntireRow, Wdth).Value
vHght = Intersect(CLL.EntireRow, Hght).Value
If UCase(Left(Intersect(CLL.EntireRow, Nm), 2)) = "WO" Then
Totl2 = Totl2 + CLL.Value * (26 * vHght + 108 * vWdth + vWdth * vHght)
Else
If UCase(Left(Intersect(CLL.EntireRow, Nm), 2)) = "WG" Then
Totl2 = Totl2 + CLL.Value * (26 * vHght + 108 * vWdth + vWdth * vHght)
End If
Totl = Totl + CLL.Value * vWdth * vHght
End If
End If
NextShould take care of it!
Matt

samohtwerdna
11-10-2005, 12:28 PM
Matt,

Thanks a lot! - wonderful work!! :cloud9: