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
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.
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.