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

[WiseApple]

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:

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.

 

[dmt32]

Hi,
welcome to forum

try this update to your code

Place ALL codes in your forms code page

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]

Hi,
welcome to forum

try this update to your code

Place ALL codes in your forms code page

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!