PDA

View Full Version : Problems with Object Variable or With block variable not set



Derck
09-29-2019, 08:40 AM
Hello, I'm really a beginner in VBA, and I'm trying to test out some stuff and learn meanwhile.

I have a table with the first column as a date and the second one as an object. I want to make a button that whenever I click it, it will check the date and compare with today's date, and then will fill with an X or any other check mark on the second column. I have been looking around and came up with this


Private Sub CheckMark_Object_A

Dim TodayDate as Range
Dim Found As Range


With Range("A2:A33")
Set Today Date = .Find(What:="today()", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
Set Found = Cells(TodayDate.Row, 2)
End With

Found.cell = "X"
End Sub

I really have just started trying stuff. I tried different functions and always get an error as "object not defined" or something like this. Any ideas/advices?

Kenneth Hobs
09-29-2019, 11:44 AM
Welcome to the forum!

I could show you how to use Find() for for that small of a range, an iterative method would suffice.

Private Sub CheckMark_Object_A()
Dim r As Range, a, i As Long


Set r = Range("A2:A33")
ReDim a(1 To r.Cells.Count)
For i = 1 To r.Cells.Count
If r(i) = Date Then a(i) = "x"
Next i
r.Offset(, 1) = WorksheetFunction.Transpose(a)
End Sub

SamT
09-29-2019, 12:46 PM
Set Today Date = .Find(What:="today()", LookAt:=xlWhole,
Exactly what String are you looking for? "today", (all lower case,) + Empty Parentheses?

also:
With Range("A2:A33")
Set TodayDate = .Find(What:="today()", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
If Not TodayDate Is nothing then Set Found = Cells(TodayDate.Row, 2)
End With

If Not Found is Nothing Then Found.cell = "X"
'However, setting Found that way is redundantly repetitive.


Finally:
Private Sub CheckMark_Object_A
Dim Found As Range

Set Found = Range("A2:A33").Find(What:="today()", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
If Not Found is Nothing Then Found.Offset(0, 1) = "X"
End Sub

Derck
09-29-2019, 04:58 PM
That worked very well, thank you! I don't fully understand how exactly that worked. Why did you had to ReDim a? Could i have used set a = 1 to r.cell.count? The iterative method was a really good idea. Thanks! I did not know about this offset function. Very interesting!

Derck
09-29-2019, 05:00 PM
I was trying to use "today()" as the function that you could normally use in a cell in excel. So I could check daily if an item "a" was purchased.

Kenneth Hobs
09-29-2019, 05:10 PM
An array is not an object so Set is not appropriate. Today() is a worksheetfunction, not a vba function. Date provides the same numerical value.

While I could have set each value in an iteration loop for each cell in the range, that is inefficient.

Redim resized the array so that it could hold all of the results to quickly write back to the offset range.

snb
09-30-2019, 04:05 AM
You are starting with the most inconsistent element of VBA: dates,
If you want tot learn VBA keep 'dates' for the last lesson.

If cell A10 is filled by "=Today()"

Use


Sub M_snb()
Sheet1.Range("A2:A33").Find("=Today()", , -4123, 1).Offset(, 1) = "X"
End Sub

If cell A10 is filled with 30-09-2019, use


Sub M_snb()
Sheet1.Range("A2:A33").Find(Date, , -4123, 1).Offset(, 1) = "X"
End Sub

If cells A10 contains 43738, numberformatted as "dd-mm-yyyy", use

Sub M_snb()
Sheet1.Range("A2:A33").Find(Date, , -4123, 1).Offset(, 1) = "X"
End Sub

Derck
10-01-2019, 04:59 PM
Thank you for the answers! It was very helpful! I thought that all worksheet functions also applied for the VBA, but it seems its not the case haha

Paul_Hossler
10-01-2019, 05:25 PM
Thank you for the answers! It was very helpful! I thought that all worksheet functions also applied for the VBA, but it seems its not the case haha

A lot of WS can be used in VBA, but not that way. Read about Application.WorksheetFunction

Some have a VBA equivalent

Kenneth Hobs
10-01-2019, 05:31 PM
You can use most all WorksheetFunction's. Type the word WorksheetFuncton and then press period for several of them. If you want to evaluate a function, you can use Evaluate(). [] can be interpreted as Evaluate() too. e.g.

MsgBox [A1]
'or
MsgBox Range("A1")

MsgBox Format(Evaluate("Today()"), "mm/dd/yyyy")
MsgBox Format([Today()], "mm/dd/yyyy")
MsgBox Format(Date, "mm/dd/yyyy")
'and so on



Whether you use VBA or WorksheetFuction or other object methods depends on the goal. Some methods are more efficient and run faster than others. Often, there are several ways to achieve a goal.