Change all values in column E between two rows?

[mir994stan]

Hello again, somehow i menage to deal wth my data entry problem, with some help from Indian youtubers. But i couldn t find any similar post. Is it possible to make a macro to change Status from "On Stock" to "Sent" in column E for all rows between two ID number, where are found given values. For example in row 15 is ID number is 6000250-15 and in row 30 is ID 6000250-30, i need to change status for all IDs in between, including starting and ending number. From Combox1 i would choose worksheet where specific ID is stored and in textbox1 and textbox2 i would enter ID numbers for search, Textbox3 contain Status value and TextBox4 =today(). And Cmdbutton 1 to run that macro. Hope that somebody can help, or at least give me some hint where/what to search. Many thanks in advace. Link: Screen shot example

 

[DanteAmor]

That is your example of the image.

From Combox1 i would choose worksheet where specific ID is stored and in textbox1 and textbox2 i would enter ID numbers for search, Textbox3 contain Status value and TextBox4 =today().

Based on that image. What data do you have in the combo, in textbox1, textbox2, textbox3, textbox4 and what should the VBA code do?

 

[mir994stan]

Im sorry for my poor explanation, in this image is my userform and data from one of the worksheets, ComboBox1 is "Name of product" and it have all worksheet names, i have worksheets for each product. And when i select a value from dropdown list, in that sheet i want to change status for given values by criteria in textbox1 and textbox2. Textbox1 "First number" and Textbox2 "LastNumber" are empty by default, i thought to write in them for which range of Serial numbers i need to change status in column "E", Combobox2 only contain two values "On Stock" and "Sent", Textbox3 needs to write date of status change in column "G". I hope this can explain what i want to achive.

 

[DanteAmor]

Try this

Private Sub CommandButton1_Click()
  Dim i As Long
  For i = 1 To Range("A" & Rows.Count).End(3).Row
    If Range("B" & i).Value = ComboBox1 And _
      Range("A" & i).Value >= TextBox1 And Range("A" & i).Value <= TextBox2 Then
      Range("E" & i).Value = ComboBox2
      Range("F" & i).Value = TextBox3
    End If
  Next
End Sub

 

[mir994stan]

Try this

Private Sub CommandButton1_Click()
  Dim i As Long
  For i = 1 To Range("A" & Rows.Count).End(3).Row
    If Range("B" & i).Value = ComboBox1 And _
      Range("A" & i).Value >= TextBox1 And Range("A" & i).Value <= TextBox2 Then
      Range("E" & i).Value = ComboBox2
      Range("F" & i).Value = TextBox3
    End If
  Next
End Sub

[/C
[/QUOTE]

Works as expected. A few lines of code saved me big time searching and replacing manualy. Thank you Sir.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[mir994stan]

Hello Dante,
I noticed i have a small problem with your code from this question, and i have no idea how to fix this. Can u give me some suggestions? As u can see in image below, code for some reason skip changing values in some rows. All values in each column are in same format. I painted red values that are skiped from change from U magacinu (On Stock) to Sent? And that happend sometimes, most of the time code runs as expected. Thank you in advance! Mirko

 

[DanteAmor]

We did the tests with
60188-01
60188-02
...
But now you used:
60188-1
60188-2
And it's not the same.

Try the following:

Private Sub CommandButton1_Click()
  Dim i As Long
  Dim part1 As String, x1 As String, y1 As String
  Dim part2 As Long, x2 As Long, y2 As Long
  
  For i = 1 To Range("A" & Rows.Count).End(3).Row
    If Range("B" & i).Value = ComboBox1 Then
      If InStr(1, Range("A" & i).Value, "-") > 0 Then
        x1 = Split(TextBox1, "-")(0)
        x2 = Split(TextBox1, "-")(1)
        y1 = Split(TextBox2, "-")(0)
        y2 = Split(TextBox2, "-")(1)
        part1 = Split(Range("A" & i).Value, "-")(0)
        part2 = Val(Split(Range("A" & i).Value, "-")(1))
        If part1 = x1 Or part1 = y1 Then
          If part2 >= x2 And part2 <= y2 Then
            Range("E" & i).Value = ComboBox2
            Range("F" & i).Value = TextBox3
          End If
        End If
      End If
    End If
  Next
End Sub

 

[mir994stan]

Oh sorry, i didn t know that... Great, its working, thank you for your time and help a lot! God bless you!

 

[DanteAmor]

Again with pleasure. Thanks for the feedback