Entering data by cell heading through Userform

[JBDC]

Hello,

I'm very new to VBA and would greatly appreciate any help on this code...

I have set up a Userform to enter data not only by category, but by date--I have both row and column headings. My userform includes a textbox (TextBox5) that I have coded to show the current date. I would like for my form entries (e.g., Boards, Bags, Boxes, Dowels, etc.) to fall into the column with the matching date heading on my spreadsheet. My userform captures quantities purchased, always integers.

Here is what I have:

Dim iColumn As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
Dim logday as Date
Set logday = TextBox5.Value

iColumn = ws.Range("B1:H1").Find(What:=logday, SearchOrder:=xlColumns, SearchDirection:xlPrevious, LookIn=Values).Column

ws.Cells(2, iColumn).Value=TextBox1.Value
ws.Cells(3, iColumn).Value=TextBox2.Value

...And so on down the row for the other textbox entries.

The debugger tells me "Object required" and highlights the text I have bolded. But I thought that defining as "Date" was an Object type.

Thanks so much for any help!

[walruseggman]

Date isn't an Object. Remove the "Set" and that error should go away.

p.s. I know this is your first post, but you need to put your code in [CODE] brackets (the # button in the editor). You'll get a notice from the moderators.

[JBDC]

Thanks! For both tips.

I removed the "Set" and got a new error: "Object variable or With block variable not set." I got this before, which is why I incorrectly thought that I needed the "set" in there. It highlights the "iColumn=..." line of code this time though. Am I defining "What" incorrectly?

[walruseggman]

You've got a couple syntax error in that line, You're missing an equal sign in SearchDirection and a colon in LookIn. Does it work after that?

Please Login or Register  to view this content.

FYI, it won't red-text a line because you have a wrong data type, just if you have syntax errors.

[JBDC]

Sorry, those were just errors I created in the posting--in the actual code reads as it should, as in your example. Still the same error, "Object variable ro With block variable not set."

[JBDC]

It works now! Unfortunately, I don't understand why. I fiddled with the "What:=" inserting different options, all of which failed. And when I put logday back in, it worked. Thanks so much for your prompt replies!!

[walruseggman]

I was just doing the same thing. Not sure what exactly was wrong, but glad it's fixed.