• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Type Mismatch

A

asparagus

Member
Hello Master,

I need Help in my program always show wraning type missmatch, I dont know where is the error code. I already check and nothing code wrong.

Code: 
Private Sub Workbook_Open()
  'fungsi untuk menjalankan sheet saat workbook open
  Worksheets("Thailand").Select 'tambahin worksheet kalo mau jalanin sheet yang berbeda
  
  With Application
  .ScreenUpdating = False
  .Calculation = xlCalculationManual
  .EnableEvents = False
  End With
  
  Dim stDate As Long: stDate = Date + 2
  Dim stDate1 As Long: stDate1 = Date + 4
  Dim count1 As Integer
  Dim sh As Worksheet
  Dim wbNew As Workbook
  
  
  count1 = 0
  
  'filter data yang sesuai kondisi Today + 3
'  With Sheet1
  ' .AutoFilterMode = False
  'with .range
  Range("A4:AP5000").AutoFilter 1, Criteria1:=">" & stDate, _
  Operator:=xlAnd, Criteria2:="<" & stDate1
  ' .AutoFilter
  
  '  End With
 ' End With
  
  
  'Application.CutCopyMode = False
  
  For Each cell In Range("A5:A5000") 'range cell
  If cell.Value = Date + 3 Then
  'Cells(3, 1).Interior.ColorIndex = 3
  'Cells(3, 1).Font.ColorIndex = 1
  'Range("A3").Value = cell.Value 'menampilkan tanggal yang sesuai dengan tanggal hari ini +3
  'Columns(3).Delete
  count1 = count1 + 1
  Else
  End If
  Next
  Range("B4:CG5000").Copy 'copy data yang sudah di filter
  Workbooks.Add 'create new workbook
  Set wbNew = ActiveWorkbook 'setting new workbook sebagai active workbook
  [A4].PasteSpecial xlPasteAll 'paste data yang di copy ke new workbook
  [A4].PasteSpecial xlPasteValues
  
  wbNew.Sheets(1).Name = "Monitoring List CKD Thailand" ' setting untuk mengubah nama sheet di workbook baru
  wbNew.SaveAs "Z:\SAP\Monitoring List CKD & Local\Reminder\Local\Reminder Monitoring Lot CKD Thailand  " & Format(Now, "dd-mmm-yy") & ".xlsx"
  wbNew.Close 'close new workbook
  
  ' kondisi sending email hanya 1 kali
  If count1 > 0 Then
  SendReminderMail
  ElseIf count1 = 0 Then
  End If
  
  'Worksheets("Monitoring List CKD F-Series").Select 'tambahin worksheet kalo mau jalanin sheet yang berbeda
  With Application
  .ScreenUpdating = True
  .Calculation = xlCalculationAutomatic
  .EnableEvents = True
  End With
End Sub

Anyone can help me please.
Thanks,
AsparAgus
 
Hi ,

When ever you face an error in code , please also specify the line which is highlighted by Excel as the line which has an error. This helps to understand the problem faster , since others do not have to go through all of the code.

Narayan
 
Dear Narayan

I'M sorry narayan because when I running my file excel unknown what line error.
I know the line error code after i restart my computer a few minute ago.

Code: 
If sell.Value = Date + 3 Then
 
Hi ,

I am confused !

Your initial post had the line :

If cell.Value = Date + 3 Then

Now , your post has :

If sell.Value = Date + 3 Then

What you have posted now is incorrect ; can you confirm which is the actual line within your running code ?

Narayan
 
Hi

I put your code into a regular module for testing. It is easier to test code when it is in a regular module as you can put break points in the code and this is not available in the worksheet open. Anyways I have run the code and it runs fine. It is curious that you used the faster autofilter to trap your dates but then used a loop to trap the date for 3 days past today. I have changed your coding to use a filter for both as this is probably a lot more efficient. I also made the assumption you have a procedure called SendReminderMail

Code: 
Option Explicit
Sub testo()
Dim stDate As Long
Dim stDate1 As Long
Dim stDate3 As Long
Dim sh As Worksheet
Dim wbNew As Workbook
Dim lr As Long
 
stDate = Date + 2
stDate1 = Date + 4
stDate3 = Date + 3
Set sh = Sheets("Thailand") 'tambahin worksheet kalo mau jalanin sheet yang berbeda
 
  sh.Range("A4:A5000").AutoFilter 1, ">" & stDate3
  lr = Range("A5000").End(xlUp).Row
  sh.Range("A4:A5000").AutoFilter 1, ">" & stDate, xlAnd, "<" & stDate1
 
  sh.Range("B4:CG5000").Copy 'copy data yang sudah di filter
  Workbooks.Add 'create new workbook
  Set wbNew = ActiveWorkbook 'setting new workbook sebagai active workbook
  [A4].PasteSpecial xlPasteAll 'paste data yang di copy ke new workbook
  [A4].PasteSpecial xlPasteValues
 
  wbNew.Sheets(1).Name = "Monitoring List CKD Thailand" ' setting untuk mengubah nama sheet di workbook baru
  wbNew.SaveAs "Z:\SAP\Monitoring List CKD & Local\Reminder\Local\Reminder Monitoring Lot CKD Thailand  " & Format(Now, "dd-mmm-yy") & ".xlsx"
  wbNew.Close 'close new workbook
 
  ' kondisi sending email hanya 1 kali
    If lr > 4 Then SendReminderMail
End Sub


Up until the sendreminderMail it works fine.

I think you had turned the calcs off etc because of the loop. No need if you use the filters IMO.

Take care

Smallman
 
Oh god i'm typo, I'm sorry

The correct code is "If cell.Value = Date + 3 Then"
error showing always show in the line code, i dont know what wrong with my code above
 
Hi Smallman,

its work in my file, but why cannot send email ? I already have function SendReminderEmail.
Can you explain what wrong smallman
 
Hi Asparagus

Nothing wrong with the code. You are asking the wrong question with the information you have supplied. You have provided everything but the email coding.

Why don't you just test that it gets to the email part with a msgbox

msgbox "We are up to here"

Instead of the email coding. This will tell you if the coding above works. Alternatively if it does not work I post you some code which you may consider using.

Take care

Smallman
 
Hi Smallman

the code works now. I'm very thank you with your help.
But why you delete code "Worksheets("Partlist").Select". i want file will running automatic when file opened.
i rewrite my code above but the file cannot running when i'm open the file
 
Asparagus

If you have a look at your coding there is no line called

Worksheets("Partlist")

You are confusing yourself. There is a worksheet called Thailand and I took the selection statement from that because in VBA using select statements adds more lines than necessary and selecting slows coding speed.

I am glad the code works.

Take care

Smallman
 
You must log in or register to reply here.
Back
Top