Solved: Date & Excel

[wildpianist]

Hey guys.

I have a spreadsheet that fills down from a UserForm when info inputted.

I have a column for each date - is there a way to get Excel to populate the desired column dependant on the date

At the moment I have

Range("C3").Select
ActiveCell.Value = TextBox1.Value
ActiveCell.Offset(1, 0) = TextBox2.Value
ActiveCell.Offset(2, 0) = TextBox3.Value
ActiveCell.Offset(3, 0) = TextBox4.Value

ActiveCell.Offset(4, 0) = TextBox5.Value

ActiveCell.Offset(5, 0) = TextBox6.Value

ActiveCell.Offset(6, 0) = TextBox7.Value

ActiveCell.Offset(7, 0) = TextBox8.Value

ActiveCell.Offset(8, 0) = TextBox9.Value

ActiveCell.Offset(9, 0) = TextBox10.Value

ActiveCell.Offset(10, 0) = TextBox11.Value

ActiveCell.Offset(11, 0) = TextBox12.Value

ActiveCell.Offset(12, 0) = TextBox13.Value

ActiveCell.Offset(13, 0) = TextBox14.Value
etc etc which populates down from C3 but I want it to look first and find the date in Row 2 and if it matches todays date fill down from there.

In C2 I have the date say 17/01/08 in D2 its 18/01/08

Can upload spreadsheet if needed.

Thanks for your help

[Bob Phillips]

Dim cell As Range
Dim i As Long

Set cell = Range("C3").Find(.Range("C2").Value, LookIn:=xlWhole)
If Not cell Is Nothing Then

For i = 1 To 10

.Offset(, 0).Value = Me.Controls("TextBox" & i).Text
Next i
End If

[wildpianist]

Thanks xld for your quick reply

What I need to do is get todays date from Date$ then I assume declare eg 'tdate' as a string so eg


dim tdate as string

tdate = Date$


Then I need it to look along from C2 -Z2 which contains dates and when it finds one that matches todays date fill down from Row 3 of that column containing today's date. (or what ever date is in tdate)

Thanks as always

[Bob Phillips]

Okay, try #2


Dim col As Long
Dim i As Long

On Error Resume Next
col = Application.Match(CLng(Date), Rows(2), 0)
On Error GoTo 0

If col > 0 Then
For i = 1 To 10

.Offset(2 + i, col).Value = Me.Controls("TextBox" & i).Text
Next i
End If

[wildpianist]

All I get is 41 coming up in a MsgBox lol

I'll upload the spreadsheet for you - sorry its mostly blank as its for a work project and highly confidential.

From C2 - AM2 there are dates.

Numbers go into the textboxes and fills under the cell with the date that equals date$.

Don't worry about the dates underneath, I'll get to that a bit later.

[Bob Phillips]

I removed the MsgBox, but you must have got a pre-amended version. It also updates the worksheet, but I was one column off



Dim col As Long
Dim i As Long

On Error Resume Next
col = Application.Match(CLng(Date), Rows(2), 0)
On Error Goto 0

If col > 0 Then
For i = 1 To 10

.Offset(2 + i, col).Value = Me.Controls("TextBox" & i).Text
Next i
End If

[wildpianist]

. [posting error] d'oh

[wildpianist]

Sorry xld - when it gets to .Offset it breaks and tells me there is an invalid or unqualified reference - I put ActiveCell before the .Offset but it still pre-fills all the info in the column next door and two rows down. I tried removing the Rows(2), 0) to Rows (0), 0) but got nothing :(

Sorry for troubling you

[wildpianist]

I've fixed the rows, but how ever hard i try - it still moves it one column to the left!!! I'm starting to hate spreadsheets now!!!

[wildpianist]

One column to the right (sorry!)

[Bob Phillips]

I didn't post the corrected vesrion for some reason



Dim col As Long
Dim i As Long

On Error Resume Next
col = Application.Match(CLng(Date), Rows(2), 0)
On Error Goto 0

If col > 0 Then
For i = 1 To 10

.Offset(2 + i, col - 1).Value = Me.Controls("TextBox" & i).Text
Next i
End If

[wildpianist]

Spot on, xld - My error was Offset(2 + i, col, - 1).Value and it didn't work. Didn't think to take the comma out!

Many Thanks Again :)

[Bob Phillips]

Private Sub CommandButton1_Click()
Const MAX_ROWS As Long = 30
Const START_ROWS As Long = 3
Dim rng As Range
Dim cell As Range
Dim col As Long
Dim i As Long
Dim NextRow As Long

With Worksheets("Team Total")

On Error Resume Next
col = Application.Match(CLng(Date), Rows(2), 0)
On Error GoTo 0

If col > 0 Then

NextRow = 2
For i = 1 To MAX_ROWS

If .Cells(i + START_ROWS - 1, col).Value = "" Then

If rng Is Nothing Then

Set rng = .Cells(i + START_ROWS - 1, col)
Else

Set rng = Union(rng, .Cells(i + START_ROWS - 1, col))
End If
End If
Next i

For i = 1 To rng.Cells.Count

.Range(rng.Cells(i, 1).Address).Value = Me.Controls("TextBox" & i).Text
Next i
End If
End With

Me.Hide
End Sub