PDA

View Full Version : Sleeper: Need help with my code as it doesnt seem to be appearing



moiishchiu
05-11-2016, 06:26 PM
Its a very long program, so this is just a part where it does not appear in the cell, also what does this mean as in delta. Im using another persons code to automate a new worksheet so im not very sure what it means.

Its not complete but I just want to make sure that at least a value is appearing in the cells I assigned it to. Its at the bottom of the coding.


DELTA = Sheets("Summary").Rows(4).Find(What:=strSearch, LookIn:=xlValues, lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, searchformat:=False).Column




Dim LCMSsqQTYnew As Integer
Dim LCMSsqDollarnew As Integer
Dim LCMSqqqQTYnew As Integer
Dim LCMSqqqDollarnew As Integer

Dim LCMStofQTYnew As Integer
Dim LCMStofDollarnew As Integer
Dim LCMSqtofQTYnew As Integer
Dim LCMSqtofDollarnew As Integer

Dim LCMSancilDollarnew As Integer

Dim AAScustQTYnew As Integer
Dim AAScustDollarnew As Integer

Dim AASinstQtynew As Integer
Dim AASinstDollarnew As Integer

Dim AASconsDollarnew As Integer

Dim AASencoreQtynew As Integer
Dim AASencoreDollarnew As Integer

Dim AASrfQtynew As Integer
Dim AASrfDollarnew As Integer

For counter2 = 4 To lengthoflist - 1 'means row 4 count down as a loop (keep adding)

If Cells(counter2, shipDate) <> "N/A" Then 'why not equal to "n/a"
If Cells(counter2, 6) = "LCMS_SQ" Then
If Cells(counter2, 8) = "NEW" Then
LCMSsqQTYnew = LCMSsqQTYnew + Cells(counter2, 4)
LCMSsqDollarnew = LCMSsqDollarnew + Cells(counter2, shipUSD)
End If
ElseIf Cells(counter2, 6) = "LCMS_QQQ" Then
If Cells(counter2, 8) = "NEW" Then '(might be else if?)
LCMSqqqQTYnew = LCMSqqqQTYnew + Cells(counter2, 4)
LCMSqqqDollarnew = LCMSqqqDollarnew + Cells(counter2, shipUSD)
End If
ElseIf Cells(counter2, 6) = "LCMS_TOF" Then
If Cells(counter2, 8) = "NEW" Then
LCMStofQTYnew = LCMStofQTYnew + Cells(counter2, 4)
LCMStofDollarnew = LCMStofDollarnew + Cells(counter2, shipUSD)
End If
ElseIf Cells(counter2, 6) = "LCMS_Q-TOF" Then
If Cells(counter2, 8) = "NEW" Then
LCMSqtofQTYnew = LCMSqtofQTYnew + Cells(counter2, 4)
LCMSqtofDollarnew = LCMSqtofDollarnew + Cells(counter2, shipUSD)
End If
ElseIf Cells(counter2, 6) = "LCMS - Others" Or Cells(counter2, 6) = "LCMS - Sources" Then
If Cells(counter2, 8) = "NEW" Then
LCMSancilDollarnew = LCMSancilDollar + Cells(counter2, shipUSD)
End If

ElseIf Cells(counter2, 6) = "V1 - Custom" Then
If Cells(counter2, 8) = "NEW" Then
AAScustQTYnew = AAScustQTYnew + Cells(counter2, 4)
AAScustDollarnew = AAScustDollarnew + Cells(counter2, shipUSD)
End If
ElseIf Cells(counter2, 6) = "V1 - Instrument" Then
If Cells(counter2, 8) = "NEW" Then
AASinstQtynew = AASinstQtynew + Cells(counter2, 4)
AASinstDollarnew = AASinstDollarnew + Cells(counter2, shipUSD)
End If
ElseIf Cells(counter2, 6) = "V1 - Consumables" Then
If Cells(counter2, 8) = "NEW" Then
AASconsDollarnew = AASconsDollarnew + Cells(counter2, shipUSD)
End If
ElseIf Cells(counter2, 6) = "V1 - Encore" Then
If Cells(counter2, 8) = "NEW" Then
AASencoreQtynew = AASencoreQtynew + Cells(counter2, 4)
AASencoreDollarnew = AASencoreDollarnew + Cells(counter2, shipUSD)
End If

ElseIf Cells(counter2, 6) = "V1 - RapidFire" Then
If Cells(counter2, 8) = "NEW" Then
AASrfQtynew = AASrfQtynew + Cells(counter2, 4)
AASrfDollarnew = AASrfDollarnew + Cells(counter2, shipUSD)
End If
End If
End If
Next counter2


Cells(5, DELTA - 2) = LCMSsqQTYnew

SamT
05-11-2016, 08:26 PM
Please only "bump" your thread one time each 24 hours. The time in the middle of the USA is now 10:25 PM.

SamT
05-11-2016, 09:14 PM
All those "End If's" after each "ElseIf" were a problem.

the Decision logic in the following code is identical to the decision logic in your code, but it will be significantly faster.

Dim LCMSsqQTYnew As Integer
Dim LCMSsqDollarnew As Double 'Use Double for currency
Dim LCMSqqqQTYnew As Integer
Dim LCMSqqqDollarnew As Double

Dim LCMStofQTYnew As Integer
Dim LCMStofDollarnew As Double
Dim LCMSqtofQTYnew As Integer
Dim LCMSqtofDollarnew As Double

Dim LCMSancilDollarnew As Double

Dim AAScustQTYnew As Integer
Dim AAScustDollarnew As Double

Dim AASinstQtynew As Integer
Dim AASinstDollarnew As Double

Dim AASconsDollarnew As Double

Dim AASencoreQtynew As Integer
Dim AASencoreDollarnew As Double

Dim AASrfQtynew As Integer
Dim AASrfDollarnew As Double

Dim Cell24 As Long
Dim Cell26 As String
Dim Cell28 As String
Dim Shipping As Double

For counter2 = 4 To lengthoflist - 1 'means row 4 count down as a loop (keep adding)
Cell28 = Cells(counter2, 8)

If LCase(Cells(counter2, shipDate)) <> "n/a" And LCase(Cell28) = "new" Then
Cell24 = Cells(counter2, 4)
Cell26 = Cells(counter2, 6)
Shipping = Cells(counter2, shipUSD)

If LCase(Left(Cell26, 2)) = "lc" Then

If Cell26 = "LCMS_SQ" Then
LCMSsqQTYnew = LCMSsqQTYnew + Cell24
LCMSsqDollarnew = LCMSsqDollarnew + Shipping

ElseIf Cell26 = "LCMS_QQQ" Then '(might be else if?)
LCMSqqqQTYnew = LCMSqqqQTYnew + Cell24
LCMSqqqDollarnew = LCMSqqqDollarnew + Shipping

ElseIf Cell26 = "LCMS_TOF" Then
LCMStofQTYnew = LCMStofQTYnew + Cell24
LCMStofDollarnew = LCMStofDollarnew + Shipping

ElseIf Cell26 = "LCMS_Q-TOF" Then
LCMSqtofQTYnew = LCMSqtofQTYnew + Cell24
LCMSqtofDollarnew = LCMSqtofDollarnew + Shipping

ElseIf Cell26 = "LCMS - Others" Or Cell26 = "LCMS - Sources" Then
LCMSancilDollarnew = LCMSancilDollar + Shipping
End If

ElseIf Lcase(Left(Cell26, 2)) = "v1" Then

If Cell26 = "V1 - Custom" Then
AAScustQTYnew = AAScustQTYnew + Cell24
AAScustDollarnew = AAScustDollarnew + Shipping

ElseIf Cell26 = "V1 - Instrument" Then
AASinstQtynew = AASinstQtynew + Cell24
AASinstDollarnew = AASinstDollarnew + Shipping

ElseIf Cell26 = "V1 - Consumables" Then
AASconsDollarnew = AASconsDollarnew + Shipping

ElseIf Cell26 = "V1 - Encore" Then
AASencoreQtynew = AASencoreQtynew + Cell24
AASencoreDollarnew = AASencoreDollarnew + Shipping


ElseIf Cell26 = "V1 - RapidFire" Then
AASrfQtynew = AASrfQtynew + Cell24
AASrfDollarnew = AASrfDollarnew + Shipping
End If
End If
Next counter2