0

Basically in my excel workbook, I have multiple worksheets with inventory data except for the first worksheet, which is meant for inputting values specifically the dates of the month, and what I want is for the code to identify the input dates from the inputbox to find column headings in those respective inventory data, and from there input the value into the desired cell in the correct row and column. Or in simple terms, is VBA able to find columns from another worksheet based on an input? Is that possible?

Here is my initial attempt for the code, but unfortunately I got stuck and did not know how to continue:

Private Sub Label1_Click()
 'input variable declaration

Dim inputValue As Variant
Dim inputValue2 As Variant  
Dim lowerLimit As Long
Dim upperLimit As Long
Dim response As String

'date range of the month
lowerLimit = 1
upperLimit = 31

'input box getting the user's date of choice
inputValue = InputBox("Please enter a Date")
If Not IsNumeric(inputValue) Then
    MsgBox "Dates can only be numeric!"
    
 Exit Sub
    Else
           
End If

If inputValue < lowerLimit Or inputValue > upperLimit Then
   MsgBox "Dates cannot be below 1 or above 31!"
   Exit Sub
End If
If IsNumeric(inputValue) Then

'2nd input box getting the user's quantity used of choice
inputValue2 = InputBox("Enter quantity used.")
   If Not IsNumeric(inputValue2) Then
    MsgBox "Quantity added can only be numeric!"
    Exit Sub
    End If
    Else
    'Part where code identifies which column to input value
    Set SearchRange = Worksheets("Sheet2").Range("D2:AH2")
     FindWhat = inputValue
     If FindWhat(inputValue) <> "" Then
     End If
 End Sub

3rd Edit: I tried my best for a pictorial representation, hopefully it is slightly easier to understand what I am trying to achieve. enter image description here

z y
  • 3
  • 2
  • 1
    If I appear to be confused, it is probably most likely because I legitimately am, since I am trying to learn VBA from scratch, sorry about that :/ – z y Nov 05 '21 at 06:45
  • https://learn.microsoft.com/en-us/office/vba/api/excel.range.find – braX Nov 05 '21 at 07:15
  • What do you mean by "numeric date"? Do you mean a day number? If so, there are months with less number of days... Are there, in a row, only day numbers and you need to find the column of a specific one? What you try doing should be possible if you will be able to better explain what you need accomplishing. **In words**. A relevant picture letting us understanding how the data where a specific cell to be found, will also help. This is not a matter of knowing VBA... – FaneDuru Nov 05 '21 at 07:42
  • Hi, thanks for the response too, I have added a relevant picture that might help. Yes, I meant day number, sorry for the misunderstanding. – z y Nov 05 '21 at 09:04
  • Hi, I assume that worksheets are named differently for ex "week1, week2..."and in theme row "1" are dates for ex. "2021-11-25, 2021-11-26..." if you input 25 you want to collect data bellow of "2021-11-25". You would need first change input into date then go through all [sheets](https://support.microsoft.com/en-us/topic/macro-to-loop-through-all-worksheets-in-a-workbook-feef14e3-97cf-00e2-538b-5da40186e2b0). Then iterate -> for each worksheet find last [column](https://stackoverflow.com/questions/11926972/how-do-i-find-the-last-column-with-data) and if col.header is right take value **offset – p77u77n77k Nov 05 '21 at 09:06
  • Now, the column where to be input a value and the value itself are clear and easy to be accomplished, but in which row to input the value? There are there 9 categories... Shouldn't it be necessary to also choose such a category? – FaneDuru Nov 05 '21 at 09:16
  • Yes, it's just another iteration though category with check of the name – p77u77n77k Nov 05 '21 at 09:30
  • OK, I will prepare an answer... – FaneDuru Nov 05 '21 at 09:52

1 Answers1

0

Please, test the next code:

Private Sub Label1_Click()
 Dim inputValue, inputValue2, lowerLimit As Long, upperLimit As Long
 Dim sh As Worksheet, lastRow As Long, dayRng As Range, material As String, arrM
 Dim El, strMat As String, i As Long, matNo, rowRng As Range
 
 lowerLimit = 1: upperLimit = 31

 'input box getting the user's date of choice
 inputValue = InputBox("Please enter a numeric Day number (between 1 and 31)")
 If Not IsNumeric(inputValue) Then
     MsgBox "Dates can only be numeric!": Exit Sub
 ElseIf inputValue < lowerLimit Or inputValue > upperLimit Then
    MsgBox "The chosen number must be between 1 and " & upperLimit & "!": Exit Sub
 End If

 '2nd input box getting the user's quantity used of choice
 inputValue2 = InputBox("Enter quantity used.")
 If Not IsNumeric(inputValue2) Then
        MsgBox "Quantity added can only be numeric!": Exit Sub
 End If

 'Part where code identifies which column to input value
 Set sh = Worksheets("Sheet2") 'use here the necessary sheet
 lastRow = sh.Range("A" & sh.rows.count).End(xlUp).row   'last row in A:A
 arrM = sh.Range("A3:A" & lastRow).value   'place the range in an array for faster iteration
 
 For Each El In arrM 'build a string to select according to the material number (in the array)
    i = i + 1
    strMat = strMat & El & " - " & i & vbCrLf
 Next El
 matNo = InputBox("Please, select the number near the material to be operated:" & vbCrLf & _
               strMat)
  If Not IsNumeric(matNo) Then
        MsgBox "Only a number is allowed...": Exit Sub
  ElseIf CLng(matNo) < 1 Or CLng(matNo) > UBound(arrM) Then
        MsgBox "You can choose only numbers between 1 and " & UBound(arrM): Exit Sub
  End If
  
 Set dayRng = sh.rows("2:2").Find(what:=inputValue, LookIn:=xlValues, lookat:=xlWhole)
    If Not dayRng Is Nothing Then
        Set rowRng = sh.Range("A3:A" & lastRow).Find(what:=arrM(CLng(matNo), 1), LookIn:=xlValues, lookat:=xlWhole)
           If Not rowRng Is Nothing Then sh.cells(rowRng.row, dayRng.Column).value = inputValue2
    End If
End Sub

It will use only a label (to save writing useless controls and code), but allowing the possibility to select the necessary material from a list.

Please, send some feedback after testing it.

FaneDuru
  • 38,298
  • 4
  • 19
  • 27