0

I use an userform to add new content to a sheet. To add content, I get an ID number (1 to ... let's say 4 for example but it will be used on larger numbers) and I am trying to send data when the condition is fulfilled (the condition being that if the number I put in a combobox on my userform is the same as the one on the sheet, then all the data written on the userform fills the rows below the ID number).

I have been stuck on this for a few hours now and just can't think of another way to do it.

Code

Dim j As Long
Dim lastRow As Long
lastRow = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row
i = 0
numero = ComboBox_numero.Value 'value of the number ID filled in the combobox
    For j = 5 To lastRow
    Sheets(5).Cells(1, 2 + i) = Sheets(2).Range("A" & j).Value 'takes the ID number from an other sheet
    Select Case numero
        Case Is = Sheets(5).Cells(1, 2 + i).Value
            'here will be the data sent below the ID number
    End Select
    i = i + 1
    Next

Any help is welcome, I can give more details if needed.

Jorondah
  • 25
  • 7
  • It's not clear why you're copying the value to sheet5 from sheet2? You can use Appliction.Match() directly on sheet2 with no need for a loop. https://stackoverflow.com/questions/37848044/vba-syntax-on-match-method/37848083#37848083 – Tim Williams Apr 17 '20 at 05:04
  • I don't get what Application.Match() does, does it pick up the data ? I'm fairly new to VBA, sorry for what can be seen as huge mistakes. I'm copying value from Sheet2 because I did a fully new sheet that only need this part of the data (it needs to be on an other sheet if this was the question). – Jorondah Apr 17 '20 at 05:29

1 Answers1

1

may be you can start from here:

Dim numero As Long
numero = CLng(ComboBox_numero.Value) 'value of the number ID filled in the combobox

Dim numeroPosition As Variant
numeroPosition = Application.Match(numero, Sheets(2).Range("A5", Sheets(2).Cells(Rows.Count, "A").End(xlUp)), 0) ' use Application.Match to find the index of the searched number inside the the array

If Not IsError(numeroPosition) Then ' if match found
    Sheets(5).Cells(1, numeroPosition - 2).Value = numero 'copy 'numero' in Sheets(5) wanted cell
        'here will be the data sent below the ID number
End If
HTH
  • 2,031
  • 1
  • 4
  • 10
  • I do not fully understand it. I get that numeroPosition is the position of numero in the array (I don't get the 0 at the end of Application.Match). I also don't get the numeroPosition - 2. I mean I get it is the columns but my columns starts at "B2" so it can possibly get a value of 0. – Jorondah Apr 17 '20 at 06:03
  • see [this link](https://learn.microsoft.com/it-it/office/vba/api/Excel.WorksheetFunction.Match) for `Match` documentation. Have you tried the code? Does it do what expected? If not, what's it not doing? – HTH Apr 17 '20 at 06:11
  • I did try the code and just as before, nothing is written in the Sheet5, no error displayed tho – Jorondah Apr 17 '20 at 06:14
  • Then it did not find an exact match of `numero` in `Sheets(2).Range A5:A...`. – HTH Apr 17 '20 at 06:15
  • Could it be because of a different type ? Range A5:A8 (stops to 8 for the moment just to try to figure out things) on sheets 2 is exclusively numbers (going from 1 to 4). – Jorondah Apr 17 '20 at 06:21
  • Yes. I edited code to convert a`String` (i.e. the return type of a Combobox.Value) to a `Long`. Should they be doubles, use `CDbl()` instead of `CLng()`. – HTH Apr 17 '20 at 06:43
  • I don't have access to it anymore until this afternoon. Will keep you tuned asap. Thanks for the help, I'll come back later. – Jorondah Apr 17 '20 at 07:04
  • Excuse me got busy the whole time, it worked well, I changed a bit how my code was written but everything is fine now. Thanks for help ! – Jorondah Apr 21 '20 at 11:47