Parse data into multiple locations based upon multiple criteria using VBA

[edpratt]

Hi,

I am trying to sort/copy/paste a set of data into multiple locations. As far as sorting and splitting basic data sets, I am comfortable. However, I need to split this based upon several criteria and whilst I can understand how to do it logically, I am struggling to turn this into VBA.

The data (small example shown below) must be separated as per the following criteria;
Temperature (<=95 / 96<Temp<=130 / 131<Temp<160) subdivided by Pressure (<100 / 101<Pressure<300 / >301).

In my head I am thinking that this means for each row:
Lookup column 3
if <=95 then
Lookup column 4
if <=100 then
select all columns from that row. copy
paste to location A
if 101<pressure<300 then
select all columns from that row. copy
paste to location B

etc.

This seems like it will result in an absolutely huge pile of if/for loops. There is surely a neater way to tackle this problem. Can anyone help out?

Region	Development	Temperature (°C)	Pressure (bar)
USA	Project 1	120	350
USA	Project 2	130	310
UK	Project 3	130	310
ASIA	Project 4	135
ASIA	Project 4	140	115
AFRICA	Project 5	85	550
AFRICA	Project 5	90	100

 

[DanteAmor]

paste to location A

What do you mean by location, is it another sheet? Is it a column on another sheet?
Where it should be pasted, always in the same row or after the last data of a specific column?
How many records do you have?

 

[edpratt]

Hi DanteAmor,
Thanks for the quick reply!

Location is on the same worksheet but could be in multiple places, i.e. I want to split the data into individual tables as per the criteria ranges (I've just put a few of them below).
This means that the data will need to be pasted onto the last row of whatever is already in those individual tables.

The total number of records is only 50ish rows, but will be added to over time as more data points are acquired.

 

[edpratt]

I realise that row selection by multiple criteria is a very common request but I cannot find anything suitable for in the forum search as my knowledge of VBA isn't good enough to assess what I am looking at with any speed.

I suppose that with the current data set, a nested loop would be the idea? I'm not too concerned with the computational speed as it doesnt need to be sorted very often.

 

[DanteAmor]

Try the following.
Just put your data on sheet1. The result will be on sheet2.

Sub MultipleLocations()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim temp As Variant, pres As Variant
  Dim lr As Long, lr2 As Long, i As Long, j As Long, lc As Long
  Application.ScreenUpdating = False
  
  Set sh1 = Sheets("Sheet1")
  Set sh2 = Sheets("Sheet2")
  temp = Array(-999, 95, 130, 160)
  pres = Array(-999, 100, 300, 999)
  
  sh2.Cells.ClearContents
  sh2.Range("A1").Value = " "
  If sh1.AutoFilterMode Then sh1.AutoFilterMode = False
  lr = sh1.Range("A" & Rows.Count).End(3).Row
  For i = 1 To UBound(temp)
    If sh1.AutoFilterMode Then sh1.AutoFilterMode = False
    lr2 = sh2.Range("A:K").Find("*", , xlValues, , xlByRows, xlPrevious).Row + 3
    lc = 1
    sh1.Range("A1:D" & lr).AutoFilter 3, ">" & temp(i - 1), xlAnd, "<=" & temp(i)
    For j = 1 To UBound(pres)
      sh1.Range("A1:D" & lr).AutoFilter 4, ">" & pres(j - 1), xlAnd, "<=" & pres(j)
      sh2.Cells(lr2, lc).Value = "Temp " & temp(i) & " Pres " & pres(j)
      sh1.AutoFilter.Range.Copy sh2.Cells(lr2 + 1, lc)
      lc = lc + 5
    Next j
  Next i
  If sh1.AutoFilterMode Then sh1.AutoFilterMode = False
End Sub

 

[edpratt]

Hi Dante,

Thanks for the code, I'm trying it out just now. Massively more simple than what I was trying to do. I wasn't aware the you could use inbuilt filtering in VBA.
This is perfect thank you

Ed

 

[DanteAmor]

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