Pulseless9
New Member
- Joined
- Jan 13, 2020
- Messages
- 2
- Office Version
- 365
- Platform
- Windows
Good evening, I have created a UserForm in a Multisheet workbook that has many functions for tracking supplies and actions throughout a regional care area. I have been able to make solid headway in teaching myself from the forums here, but I am stuck on the search for Date in a column range and updating the existing data in the row that contains the date. I have pasted my code below.
My goal is:
CommandButton12 --> find date from textbox36 in sr.range("B:B") --> update cells "C" and "D" adjacent to found date in "B".
What happens is:
The entire column "B:B" is found and the entire Column "C" and "D" are updated with data causing an overwrite of data on dates unrelated to the date searched on.
My goal is:
CommandButton12 --> find date from textbox36 in sr.range("B:B") --> update cells "C" and "D" adjacent to found date in "B".
What happens is:
The entire column "B:B" is found and the entire Column "C" and "D" are updated with data causing an overwrite of data on dates unrelated to the date searched on.
VBA Code:
Private Sub CommandButton12_Click()
Dim sr As Worksheet
Set sr = ThisWorkbook.Sheets("Regional Summary")
Dim r As Long
Dim LrR As Long
LrR = sr.Range("A" & Rows.Count).End(xlUp).Row 'Regional Summary
r = Application.Match((CLng(CDate(Me.TextBox36.Value))), sr.Range("B:B"), 0) 'Regional Summary
For r = 2 To LrR
If WorksheetFunction.CountIf(sr.Range("B:B"), (CLng(CDate(Me.TextBox36.Value)))) > 0 Then
sr.Range("D" & r).Value = Val(Me.TextBox35.Value) + Val(Me.TextBox62.Value) 'combination of all supplies sent to all sites on date in textbox36
sr.Range("C" & r).Value = Val(Me.TextBox61.Value) + Val(Worksheets("Regional Summary").Cells(r, 3).Text) 'combination of all supplies received; will likely have to pull data to hidden TB and add tb61 to it
End If
Next r
Dim txt
For Each txt In Frame2.Controls 'apply to textboxes in frame 2
If TypeOf txt Is MSForms.TextBox Or TypeOf txt Is MSForms.ComboBox Then
txt.Text = ""
txt.BackColor = vbWhite
End If
Next txt
MsgBox "Supply Receipt Recorded", vbInformation
End Sub