help in vba

[asafraz81]

i have seen the lesson in utube called vba crash course.

i copyied the module as is to visual basic.

the problem is that i get an eror while trying to run the code.

i add the code to the post with the debug line with three Asterisks.

i wil be glad if u cuold help me solve this, i want to study this complex code and be able to build macro this level.

thanks

asaf raz (from israel)

Function checksales(inthisrange As Range) As Boolean

checksales = WorksheetFunction.CountA(inthisrange) > 0

End Function


Sub processales()

If checksales(Range("c7:c30")) = False Then

Debug.Print "no sales"

Exit Sub

End If


Dim storesales As Range

Dim sumofsales As Double, cursale As Double

Dim countofsales As Integer

Dim minsale As Double, maxsale As Double

Dim reason As String, massage As String, minstore As String, maxstore As String

sumofsales = 0

countofsales = 0

minsale = 99999#

maxsale = 0

For Each storesales In Range("c7:c30")

If storesales.Value <> "" And storesales.Value <> 0 Then

***cursale = storesales.Value

sumofsales = sumofsales + cursale

countofsales = countofsales + 1


minstore = IIf(cursale < minsale, storesales.Offset(, -1).Value, minstore)

minsale = IIf(cursale < minsale, cursale, minsale)

maxstore = IIf(cursale > maxsale, storesales.Offset(, -1).Value, maxstore)

maxsale = IIf(cursale > maxsale, currentsale, maxsale)


If cursale < 500 Or currentsale > 5000 Then

reason = InputBox("reason for low", storesales.Offset(, -1).Value)

storesales.Offset(, -1).Value = reason

End If

End If

Next storesales


If countofsales > 0 Then

Message = "we operateda total of " & countofsales & "stores today" & vbCrLf

Message = Message & "we made a total of" & Format(sumofsales, "$#,#.00") & vbCrLf

Message = Message & "max sales we had is " & Format(maxsale, "$#,#.00") & "from" & maxstore & vbCrLf

Message = Message & "min sale we had is " & Format(minsale, "$#,#.00") & "from" & minstore & vbCrLf


MsgBox massage, vbInformation + vbOKOnly, "daily sale status- " & Format(Now, "dddd,mmmm,d,yyyy")

[valdailylogtitle] = "daily sales status- " & Format(Now, "dddd,mmmm,d,yyyy")

[valdailylogsummary] = Message

Range("areadailylog").ExportAsFixedFormat xlTypePDF, ThisWorkbook.Path & "/dailysaleslog-" & Format(Now, "ddmmyyyy-hhm") & ".pdf"

[valdailylogtitle] = ""

[valdailylogsummary] = ""

End If

End Sub

 

[Wookiee]

Asaf, I'm not certain exactly how this report is supposed to be structured, but I copied your code into a module and worked out all the kinks I could identify. Below is the revised code I came up with.


In a few cases, you had an undeclared variable (cursale was declared, but in some places it was currentsale; the same goes for message and massage). Also, I added indentation for the If-End Ifs and For-Next constructions to make them more easy to read. I placed some dummy data in cells B7:C26 and named that range "dailyarealog". Finally, I commented out the lines that started with "[valdaily..." because that appears to reference a non-existent object. Maybe a user form? I don't know. But once I did all that, the macro worked for me.


Below is the code I came up with. And below that is a link to the file I ended up with. I hope this helps.

[pre]

Function checksales(inthisrange As Range) As Boolean

checksales = WorksheetFunction.CountA(inthisrange) > 0

End Function

Sub processales()

If checksales(Range("c7:c30")) = False Then
Debug.Print "no sales"
Exit Sub
End If

Dim storesales As Range
Dim sumofsales As Double, cursale As Double
Dim countofsales As Integer
Dim minsale As Double, maxsale As Double
Dim reason As String, message As String, minstore As String, maxstore As String

sumofsales = 0
countofsales = 0
minsale = 99999#
maxsale = 0

For Each storesales In Range("c7:c30")
If storesales.Value <> "" And storesales.Value <> 0 Then
cursale = storesales.Value
sumofsales = sumofsales + cursale
countofsales = countofsales + 1

minstore = IIf(cursale < minsale, storesales.Offset(, -1).Value, minstore)
minsale = IIf(cursale < minsale, cursale, minsale)
maxstore = IIf(cursale > maxsale, storesales.Offset(, -1).Value, maxstore)
maxsale = IIf(cursale > maxsale, cursale, maxsale)

If cursale < 500 Or cursale > 5000 Then
reason = InputBox("reason for low", storesales.Offset(, -1).Value)
storesales.Offset(, -1).Value = reason
End If
End If
Next storesales

If countofsales > 0 Then
message = "we operated a total of " & countofsales & " stores today" & vbCrLf
message = message & "we made a total of " & Format(sumofsales, "$#,#.00") & vbCrLf
message = message & "max sales we had is " & Format(maxsale, "$#,#.00") & " from " & maxstore & vbCrLf
message = message & "min sale we had is " & Format(minsale, "$#,#.00") & " from " & minstore & vbCrLf

MsgBox message, vbInformation + vbOKOnly, "daily sale status- " & Format(Now, "dddd,mmmm,d,yyyy")
'    [valdailylogtitle] = "daily sales status- " & Format(Now, "dddd,mmmm,d,yyyy")
'    [valdailylogsummary] = message
Range("areadailylog").ExportAsFixedFormat xlTypePDF, ThisWorkbook.Path & "/dailysaleslog-" & Format(Now, "ddmmyyyy-hhm") & ".pdf"
'    [valdailylogtitle] = ""
'    [valdailylogsummary] = ""
End If

End Sub

[/pre]

https://docs.google.com/open?id=0B03QVPPcP2UwMUpTc0ZqS1BnTWc

 

[asafraz81]

thanks now i will check if its working and check my copied macro