check in a specfic column in excel wbk for yest date

[daillest319]

i wanted to check AD for yesterday's date using an if statement if the column has yest date or if it doesn't


i know in vba i have it written like so just dont know how to conver it to vbs

Sub Macro1()
    For Each cell In Range("AD1:AD" & Cells(Rows.Count, "AD").End(xlUp).Row)
        If IsDate(cell.Value) Then
            If cell.Value = Date - 1 Then
               MsgBox "date is correct"
            Else
               MsgBox "date is incorrect"
            End If
        End If
    Next
End Sub

 

[SkipVought]

You can use the same code in VBS. You just need to reference the Excel application object, the workbook and the worksheet to your existing code.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[daillest319]

Not sure where im making my error...i ended up using a different code then i org wrote. the one i had kept looping...
if anyone can help me towards the right the right direction to get this going in vbs i would really apperciate it.

dim Dim FindString As String, Rng as range

xlApp.Screenupdating = False
xlApp.Workbooks.Open "C:\Test\WORKBOOK1.xls"


With xlApp.Workbooks("WORKBOOK1.xls").Sheets("TEST")
        FindString = date - 1
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
        End With

If rng is nothing then
msgbox "Date is incorrect."
else
msgbox "Date is correct."
end if


set rng = nothing 
xlApp.ActiveWorkbook.Save

xlApp.Quit
Set xlApp = Nothing

 

[SkipVought]

...and the specific problem?

Please provide adequate information about the symptoms you observe and the things that you have tried.

INFORMATION PLEASE!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[daillest319]

the error im recieving is
Line:11
Char:33
error: Expected')'
code:800A03EE

also right now i use notpad to write vbscript's is there a better program to write them?

 

[PHV]

Anyway, this line is completly wrong in VBS:
dim Dim FindString As String, Rng as range

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

You must reference the range you want to search. I used ALL CELLS on the sheet, for instance...

With xlApp.Workbooks("WORKBOOK1.xls").Sheets("TEST")
        FindString = date - 1
            Set Rng = [highlight].cells[/highlight].Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
        End With

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[daillest319]

i got it running but it not giving me the correct msg its keeps saying its incorrect when it should be correct as the date is there...and its like and inifite loop

Dim FindString, Rng, xlApp, rFound
Set xlApp = Createobject("Excel.Application")
xlApp.Visible = False


xlApp.Screenupdating = False
xlApp.Workbooks.Open "C:\Workbook1.xls"


With xlApp.Workbooks("Workbook1").Sheets("test").Range("AD:AD")
      do
	FindString = date - 1
            set rFound = .cells.find("FindString")
      

   if not rFound is nothing then
       msgbox "Date is incorrect."
     else
       msgbox "Date is correct."
     end if
   loop



End With


set rng = nothing 

xlApp.ActiveWorkbook.Save
xlApp.ActiveWorkbook.Close	
xlApp.Quit
Set xlApp = Nothing

 

[daillest319]

I removed the do and loop. which took care of the loop but im still receieving the wrong msg back.. even tried using

set rFound = .Columns("AD:AD").find("FindString")

but it still sends back "Date is incorrect." when the date is there. a i missing something?

 

[SkipVought]

???

set rFound = .cells.find("FindString")

Are you REALLY looking for string [highlight]FindString[/highlight]???

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jges]

I also think your if statement logic is reversed from what you want.

   if not rFound is nothing then  [COLOR=green]'not is nothing = is something[/color]
       msgbox "Date is incorrect."
     else                      [COLOR=green]' is nothing[/color]
       msgbox "Date is correct."
     end if
   loop

If the find function is successful and returns a range object, the code will say "Date is incorrect."

If the find function is unsuccessful and nothing is returned, the code will say "Date is correct."

Test with an incorrect date in your range to verify.

My test code is below (workbook name/location and search range have been changed):

Dim FindString, xlApp, rFound
Set xlApp = Createobject("Excel.Application")
xlApp.Visible = False

xlApp.Screenupdating = False
xlApp.Workbooks.Open "C:\Temp\Book1.xlsx"

	FindString = DateAdd("d", -1, date)
    set rFound = xlApp.Range("A:A").find(FindString)

   if rFound is nothing then
       msgbox "Date is incorrect."
     else
       msgbox "Date is correct."
     end if

xlApp.ActiveWorkbook.Close	
xlApp.Quit
Set xlApp = Nothing

 

[daillest319]

Thanks jges works perfect. i was also able to figure out how to do it another way...ill post at the bottom incase other would like to see...but thanks again SkipVought and jges for all you help..im really new to writing scripts so thanks for tolerating me.

Dim xlApp
Set xlApp = Createobject("Excel.Application")


xlApp.Screenupdating = False
xlApp.Workbooks.Open "C:\workbook1.xls"
With xlApp.Workbooks("workbook1.xls").Sheets("test")


if .Range("A:A").End(-4121).Value <> Date-1 Then


wscript.echo "Date is incorrect."
xlApp.ActiveWorkbook.Save
xlApp.ActiveWorkbook.Close
xlApp.Quit
Set xlApp = Nothing
wscript.quit
end if

End With

End If

 

[SkipVought]

Why are you SAVING the workbook, if nothing has changed in the workbook?

Also, just a tweek, ESECIALLY your Control Structure With...End With, If...Then...End If should NEVER CROSS. Each block structure should be totally within any Higher block structure. And why SET an Object when a With..End With will suffice

    With CreateObject("Excel.Application")
        .ScreenUpdating = False
        
        With .Workbooks.Open("C:\workbook1.xls")
        
             If .Sheets("test").Range("A:A").End(-4121).Value <> Date - 1 Then
            
                 wscript.echo "Date is incorrect."
             End If
            
            .Save   '[highlight]WHY???[/highlight]
            .Close
        End With
        
        .Quit
    End With
    
    wscript.Quit

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]