Update all values in column "E" between two given ID value.

MirkoSRB · Jul 30th 2021

Hello to everyone, this is my first question here. I was looking almost everywhere but i couldnt find solution for my problem. Im not good at VBA, and i ask for help. I have one workbook with 35 worksheets, each sheet for one product, all values are in range "A:G". I created multipage userform that can add product to worksheet that i select in combobox, and that all works perfect. Now, i need a macro that can chage status value for all product in given ID range, ID value is in column "A". For example, one ID is 6000550-1 and other is 6000550-10, i want to update all cells in column "E" between that two ID, By default status value is "On Stock" and i want to change it to "Sent"

On my userform i have :

Code

ComboBox2 = Choose which worksheet have wanted IDs
TextBox1= Enter First ID
TextBox2=Enter Second ID
ComboBox3= Choose status ("Sent" & "On Stock")
Textbox3=Enter date when chage/update was made to "G" column
Comadnbutton_3  =  run command

I don t know is this possible like i imagined, but thanks in advance! Any help is welcome.

Screenshot of example is attached below.

gijsmo · Jul 31st 2021

Without seeing a sample workbook, below are examples of macros that could update the status values and dates as you've described

Code

Sub CreateOnStockList()
  Dim dDate As Date
  
  'if a date is to passed in, need to convert to equivalent long value
  'for example
  dDate = DateValue("30-JUL-2021")

  UpdateStockList ThisWorkbook.Sheets(1), "Sent", "S-400139-30A", "6000188-01", "6000188-18", CLng(dDate)
End Sub


Private Sub UpdateStockList(wsSht As Worksheet, ByVal sStatus As String, ByVal sProduct As String, _
                            ByVal sFirst As String, ByVal sLast As String, Optional ByVal lDate As Long = 0)
  
  Dim rSrc As Range, rRng As Range
  
  With Application
    .ScreenUpdating = False: .DisplayAlerts = False: .EnableEvents = False
  End With
  
  'set the date to today's date if no date value was passed in
  If lDate = 0 Then lDate = Now()
  
  With wsSht
      'turn of any autofiltering
    .AutoFilterMode = False

    'get the overall source range
    Set rSrc = .Range("A1").CurrentRegion
  
    'filter for matching rows
    'column A is assumed to contain the Number values
    rSrc.AutoFilter Field:=.Range("A1").Column, Criteria1:=">=" & sFirst, Operator:=xlAnd, Criteria2:="<=" & sLast
    
    'column B is assumed to contain the Product number
    rSrc.AutoFilter Field:=.Range("B1").Column, Criteria1:=sProduct

    'see if there are any matching rows
    Set rRng = rSrc.SpecialCells(xlCellTypeVisible)
  
    'there will be at least one row (the header), see if there are more
    If rRng.SpecialCells(xlCellTypeVisible).Areas(1).Rows.Count = 1 And _
       rRng.SpecialCells(xlCellTypeVisible).Areas.Count = 1 Then
    Else
      'remove the header row before continuing
      Set rRng = rSrc.Offset(RowOffset:=1).Resize(Rowsize:=rSrc.Rows.Count - 1)

      'column E is assumed to be the Status value column
      rRng.Columns("E").Value = sStatus
    
      'column G is assumed to be the Date values column
      rRng.Columns("G").Value = lDate
    End If
    
    'turn off the autofilter
    .AutoFilterMode = False
  End With

  With Application
    .ScreenUpdating = True: .DisplayAlerts = True: .EnableEvents = True
  End With
  
  'for completeness
  Set rSrc = Nothing: Set rRng = Nothing
End Sub

Display More

**royUK** · Jul 31st 2021

Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post

All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.

How to use code tags

Just highlight all of the code and press the <> in the post menu above button to add the code tags.

Thanks.

**royUK** · Jul 31st 2021

Attach an example workbook

Update all values in column "E" between two given ID value.

Participate now!

Share

Tags