VBA code to find date in range and then post data in form to cells along row

WiseApple

New Member
Joined
Dec 20, 2018
Messages
2
Hi all,

VBA noob here, so please bear with me.

I've built a userform to allow me to enter data and then put it into a set of cells from row D onwards based on the date entered by finding it in a range (C8:C267) in the sheet to be filled.

My code is pasting the data, but is not placing it in the correct location and seems to be just taking it in the first thirty cells in the sheet.

How do I get it to take the date into account?

My attempt:

Code:
Private Sub UserForm_Initialize()
Me.DayCode.Text = Format(Date, "dd/mm/yyyy", vbMonday, vbUseSystem)
End Sub

Private Sub StoreData_Click()
Dim irow As Long
Dim ws As Worksheet
Dim dr As Range
Dim dt As String


Set ws = Worksheets("Data")
Set dr = Date
'find appropriate row in spreadsheet
irow = Me.DayCode.Value
'copy the data to the database


With ws
Set dr = ws.Range.Find("C7:C268").Find(dt, LookIn:=xlFormulas)
  .Cells(irow, 3).Value = Me.GrossIntake.Value
  .Cells(irow, 4).Value = Me.PODAPP.Value
  .Cells(irow, 5).Value = Me.CoreAPP.Value
  .Cells(irow, 6).Value = Me.AAPP.Value
  .Cells(irow, 7).Value = Me.BAPP.Value
  .Cells(irow, 8).Value = Me.CAPP\.Value
  .Cells(irow, 9).Value = Me.PODTitle.Value
  .Cells(irow, 10).Value = Me.CoreTitle.Value
  .Cells(irow, 11).Value = Me.ATitle.Value
  .Cells(irow, 12).Value = Me.BTitle.Value
  .Cells(irow, 13).Value = Me.CTitle.Value
  .Cells(irow, 14).Value = Me.PodUnit.Value
  .Cells(irow, 15).Value = Me.CoreUnit.Value
  .Cells(irow, 16).Value = Me.AUnit.Value
  .Cells(irow, 17).Value = Me.BUnit.Value
  .Cells(irow, 18).Value = Me.CUnit.Value
  .Cells(irow, 19).Value = Me.OCRMUnit.Value
  .Cells(irow, 20).Value = Me.CaseworkUnits.Value
  .Cells(irow, 21).Value = Me.RejectedAPPs.Value
  .Cells(irow, 22).Value = Me.Under48HourStock.Value
  .Cells(irow, 23).Value = Me.Over48HourStock.Value
  .Cells(irow, 24).Value = Me.PodResource.Value
  .Cells(irow, 25).Value = Me.CoreResource.Value
  .Cells(irow, 26).Value = Me.AResource.Value
  .Cells(irow, 27).Value = Me.BResource.Value
  .Cells(irow, 28).Value = Me.CResource.Value
  .Cells(irow, 29).Value = Me.MonthlyIntake.Value
  .Cells(irow, 30).Value = Me.Day1.Value
  .Cells(irow, 31).Value = Me.Day2.Value
  .Cells(irow, 32).Value = Me.Day3.Value
  
  


End With


End Sub
Thanks in advance.
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,487
Office Version
2013
Platform
Windows
Hi,
welcome to forum

try this update to your code

Place ALL codes in your forms code page

Code:
Option Base 1


Private Sub UserForm_Initialize()
    Me.DayCode.Text = Format(Date, "dd/mm/yyyy", vbMonday, vbUseSystem)
End Sub


Private Sub StoreData_Click()
    Dim ws As Worksheet
    Dim dr As Range
    Dim dt As String
    Dim r As Integer
    
    Set ws = ThisWorkbook.Worksheets("Data")
    
    dt = Me.DayCode.Text
    
    If Not IsDate(dt) Then


        MsgBox "Please Enter Valid Date", 48, "Invalid Date"
        
    Else
        
'search for date
        Set dr = ws.Columns(3).Find(DateValue(dt), LookIn:=xlValues, lookat:=xlWhole)
        
        If Not dr Is Nothing Then
'copy the data to the database
            dr.Offset(, 1).Resize(, UBound(ControlsArray)).Value = ControlsArray
                
            Else
'inform user
            MsgBox dt & Chr(10) & "Record Not Found", 48, "Not Found"
                
        End If
    End If
End Sub




Function ControlsArray() As Variant
    ControlsArray = Array(Me.GrossIntake, Me.PODAPP, Me.CoreAPP, Me.AAPP, Me.BAPP, _
                            Me.CAPP, Me.PODTitle, Me.CoreTitle, Me.ATitle, Me.BTitle, _
                            Me.CTitle, Me.PodUnit, Me.CoreUnit, Me.AUnit, Me.BUnit, _
                            Me.CUnit, Me.OCRMUnit, Me.CaseworkUnits, Me.RejectedAPPs, Me.Under48HourStock, _
                            Me.Over48HourStock, Me.PodResource, Me.CoreResource, Me.AResource, Me.BResource, _
                            Me.CResource, Me.MonthlyIntake, Me.Day1, Me.Day2, Me.Day3)
End Function
Note Option Base 1 statement - this MUST be at very TOP of your forms code page OUTSIDE any procedure.


Dave
 

WiseApple

New Member
Joined
Dec 20, 2018
Messages
2
Hi,
welcome to forum

try this update to your code

Place ALL codes in your forms code page

Code:
Option Base 1


Private Sub UserForm_Initialize()
    Me.DayCode.Text = Format(Date, "dd/mm/yyyy", vbMonday, vbUseSystem)
End Sub


Private Sub StoreData_Click()
    Dim ws As Worksheet
    Dim dr As Range
    Dim dt As String
    Dim r As Integer
    
    Set ws = ThisWorkbook.Worksheets("Data")
    
    dt = Me.DayCode.Text
    
    If Not IsDate(dt) Then


        MsgBox "Please Enter Valid Date", 48, "Invalid Date"
        
    Else
        
'search for date
        Set dr = ws.Columns(3).Find(DateValue(dt), LookIn:=xlValues, lookat:=xlWhole)
        
        If Not dr Is Nothing Then
'copy the data to the database
            dr.Offset(, 1).Resize(, UBound(ControlsArray)).Value = ControlsArray
                
            Else
'inform user
            MsgBox dt & Chr(10) & "Record Not Found", 48, "Not Found"
                
        End If
    End If
End Sub




Function ControlsArray() As Variant
    ControlsArray = Array(Me.GrossIntake, Me.PODAPP, Me.CoreAPP, Me.AAPP, Me.BAPP, _
                            Me.CAPP, Me.PODTitle, Me.CoreTitle, Me.ATitle, Me.BTitle, _
                            Me.CTitle, Me.PodUnit, Me.CoreUnit, Me.AUnit, Me.BUnit, _
                            Me.CUnit, Me.OCRMUnit, Me.CaseworkUnits, Me.RejectedAPPs, Me.Under48HourStock, _
                            Me.Over48HourStock, Me.PodResource, Me.CoreResource, Me.AResource, Me.BResource, _
                            Me.CResource, Me.MonthlyIntake, Me.Day1, Me.Day2, Me.Day3)
End Function
Note Option Base 1 statement - this MUST be at very TOP of your forms code page OUTSIDE any procedure.


Dave
That's done the trick. Thanks Dave!
 

Watch MrExcel Video

Forum statistics

Threads
1,089,762
Messages
5,410,276
Members
403,306
Latest member
ekastan

This Week's Hot Topics

Top