View Full Version : Solved: Date & Excel
wildpianist
01-17-2008, 11:52 AM
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
01-17-2008, 12:02 PM
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
01-17-2008, 12:14 PM
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
01-17-2008, 12:24 PM
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
01-17-2008, 01:22 PM
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
01-17-2008, 02:42 PM
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
01-17-2008, 02:51 PM
. [posting error] d'oh
wildpianist
01-17-2008, 02:53 PM
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
01-17-2008, 03:32 PM
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
01-17-2008, 03:32 PM
One column to the right (sorry!)
Bob Phillips
01-17-2008, 05:47 PM
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
01-18-2008, 01:42 AM
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
01-18-2008, 07:19 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.