Hi, first post here so apoligies for any faux pas
I'm in the process of writing (what for me is) quite a long macro. The overall aim is to populate two columns with stores data drawn from the left hand side of the sheet.
It starts by chekcing two columns (W&R), finding out which column has the least number of entries and populating this column with the store number.
Stores can be of different sizes, so I check if its 4,3,2,1. Dependant on the size of the store it could occupy a number of cells in the chosen column. I've got this bit working ok.
The problem occurs where some cells in the column shouldn't be used as they symbolise busy locations. In the column these cells contain an 'x'. So I'm trying to develop an if statement that says, 'If the range you are going to paste the values into contains an x then offset the range by one row and try again' with a loop to continue doing this until it finds somewhere.
I hope this makes sense (but sure it won't
)
Here is the code I've created so far and thanks in advance for any help...
I'm in the process of writing (what for me is) quite a long macro. The overall aim is to populate two columns with stores data drawn from the left hand side of the sheet.
It starts by chekcing two columns (W&R), finding out which column has the least number of entries and populating this column with the store number.
Stores can be of different sizes, so I check if its 4,3,2,1. Dependant on the size of the store it could occupy a number of cells in the chosen column. I've got this bit working ok.
The problem occurs where some cells in the column shouldn't be used as they symbolise busy locations. In the column these cells contain an 'x'. So I'm trying to develop an if statement that says, 'If the range you are going to paste the values into contains an x then offset the range by one row and try again' with a loop to continue doing this until it finds somewhere.
I hope this makes sense (but sure it won't
)Here is the code I've created so far and thanks in advance for any help...
Code:
Sub Generate_Chill_Locations()
Dim rngStoreNrs As Range
Dim cStore As Range, cNextLocation As Range
Dim SplSize As Long, QtyReqd As Long
Dim dog As Long
Dim qwe As Long
Dim asd As Long
Dim wer As Long
Dim sdf As Long
Dim rMycell As Range
Dim Bnm As Long
Dim MySum As Long
Application.ScreenUpdating = True
With Worksheets("Generate Chill Grid")
Range("R3").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
'sets row number of cell as variable
qwe = ActiveCell.Row
wer = ActiveCell.Offset(0, -2).Value
'selects sheet
Sheets("Generate Chill Grid").Select
'finds next available cell
Range("W3").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
'sets row number as variable
asd = ActiveCell.Row
sdf = ActiveCell.Offset(0, -2).Value
'puts both variables on sheet
Range("R3").Value = qwe
Range("W3").Value = asd
Range("P3").Value = wer
Range("T3").Value = sdf
'finds out which is lower and selects lowest
If Range("R3").Value < Range("W3").Value Then
Sheets("Generate Chill Grid").Select
Range("R3").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
'insert if statement=if K3 same as zxc then paste as value else offset a row
Else
Sheets("Generate Chill Grid").Select
Range("W3").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
End If
Set rMycell = ActiveCell
Set cNextLocation = rMycell
Set rngStoreNrs = .Range("C5:C" & .Cells(Rows.Count, "C").End(xlUp).Row)
For Each cStore In rngStoreNrs
Range("R3").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
'sets row number of cell as variable
qwe = ActiveCell.Row
wer = ActiveCell.Offset(0, -2).Value
'selects sheet
Sheets("Generate Chill Grid").Select
'finds next available cell
Range("W3").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
'sets row number as variable
asd = ActiveCell.Row
sdf = ActiveCell.Offset(0, -2).Value
'puts both variables on sheet
Range("R3").Value = qwe
Range("W3").Value = asd
Range("P3").Value = wer
Range("T3").Value = sdf
'finds out which is lower and selects lowest
If Range("R3").Value < Range("W3").Value Then
Sheets("Generate Chill Grid").Select
Range("R3").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
'insert if statement=if K3 same as zxc then paste as value else offset a row
Else
Sheets("Generate Chill Grid").Select
Range("W3").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
End If
1:
Set rMycell = ActiveCell
Set cNextLocation = rMycell
'Gets size of current empty location
SplSize = cNextLocation.Offset(0, -2)
'IF statements based on size of location SPL to read correct quantity required for store
If SplSize = 4 Then
QtyReqd = cStore.Offset(0, 2)
Range("K3").Value = SplSize
Range("L3").Value = QtyReqd
If ActiveCell.Column = 18 Then
Bnm = (QtyReqd + qwe) - 1
dog = Application.WorksheetFunction.Sum(Range("R" & qwe, "R" & Bnm))
ElseIf ActiveCell.Column = 23 Then
Bnm = (QtyReqd + asd) - 1
dog = Application.WorksheetFunction.Sum(Range("W" & asd, "W" & Bnm))
End If
If dog < 1 Then
If ActiveCell.Column = 18 Then
Bnm = (QtyReqd + qwe) - 1
MySum = Application.WorksheetFunction.Sum(Range("P" & qwe, "P" & Bnm))
ElseIf ActiveCell.Column = 23 Then
Bnm = (QtyReqd + asd) - 1
MySum = Application.WorksheetFunction.Sum(Range("U" & asd, "U" & Bnm))
End If
If MySum = Range("M3").Value Then
'Puts quantity of stores in empty locations
cNextLocation.Resize(QtyReqd, 1) = cStore.Value
Else
asd = ActiveCell.Row
qwe = ActiveCell.Row
ActiveCell.Offset(1, 0).Select
GoTo 1:
End If
End If
End If
If SplSize = 3 Then
QtyReqd = cStore.Offset(0, 3)
Range("K3").Value = SplSize
Range("L3").Value = QtyReqd
If ActiveCell.Column = 18 Then
Bnm = (QtyReqd + qwe) - 1
dog = Application.WorksheetFunction.Sum(Range("R" & qwe, "R" & Bnm))
ElseIf ActiveCell.Column = 23 Then
Bnm = (QtyReqd + asd) - 1
dog = Application.WorksheetFunction.Sum(Range("W" & asd, "W" & Bnm))
End If
If dog < 1 Then
If ActiveCell.Column = 18 Then
Bnm = (QtyReqd + qwe) - 1
MySum = Application.WorksheetFunction.Sum(Range("P" & qwe, "P" & Bnm))
ElseIf ActiveCell.Column = 23 Then
Bnm = (QtyReqd + asd) - 1
MySum = Application.WorksheetFunction.Sum(Range("U" & asd, "U" & Bnm))
End If
If MySum = Range("M3").Value Then
cNextLocation.Resize(QtyReqd, 1) = cStore.Value
Else
asd = ActiveCell.Row
qwe = ActiveCell.Row
ActiveCell.Offset(1, 0).Select
GoTo 1:
End If
End If
End If
If SplSize = 2 Then
QtyReqd = cStore.Offset(0, 4)
Range("K3").Value = SplSize
Range("L3").Value = QtyReqd
If ActiveCell.Column = 18 Then
Bnm = (QtyReqd + qwe) - 1
dog = Application.WorksheetFunction.Sum(Range("R" & qwe, "R" & Bnm))
ElseIf ActiveCell.Column = 23 Then
Bnm = (QtyReqd + asd) - 1
dog = Application.WorksheetFunction.Sum(Range("W" & asd, "W" & Bnm))
End If
If dog < 1 Then
If ActiveCell.Column = 18 Then
Bnm = (QtyReqd + qwe) - 1
MySum = Application.WorksheetFunction.Sum(Range("P" & qwe, "P" & Bnm))
ElseIf ActiveCell.Column = 23 Then
Bnm = (QtyReqd + asd) - 1
MySum = Application.WorksheetFunction.Sum(Range("U" & asd, "U" & Bnm))
End If
If MySum = Range("M3").Value Then
cNextLocation.Resize(QtyReqd, 1) = cStore.Value
Else
asd = ActiveCell.Row
qwe = ActiveCell.Row
ActiveCell.Offset(1, 0).Select
GoTo 1:
End If
End If
' Else: asd = ActiveCell.Row
' qwe = ActiveCell.Row
' ActiveCell.Offset(1, 0).Select
' GoTo 1:
End If
If SplSize = 1 Then
QtyReqd = cStore.Offset(0, 5)
Range("K3").Value = SplSize
Range("L3").Value = QtyReqd
If ActiveCell.Column = 18 Then
Bnm = (QtyReqd + qwe) - 1
dog = Application.WorksheetFunction.Sum(Range("R" & qwe, "R" & Bnm))
ElseIf ActiveCell.Column = 23 Then
Bnm = (QtyReqd + asd) - 1
dog = Application.WorksheetFunction.Sum(Range("W" & asd, "W" & Bnm))
End If
If dog < 1 Then
If ActiveCell.Column = 18 Then
Bnm = (QtyReqd + qwe) - 1
MySum = Application.WorksheetFunction.Sum(Range("P" & qwe, "P" & Bnm))
ElseIf ActiveCell.Column = 23 Then
Bnm = (QtyReqd + asd) - 1
MySum = Application.WorksheetFunction.Sum(Range("U" & asd, "U" & Bnm))
End If
If MySum = Range("M3").Value Then
cNextLocation.Resize(QtyReqd, 1) = cStore.Value
Set cNextLocation = cNextLocation.Offset(QtyReqd)
Else
asd = ActiveCell.Row
qwe = ActiveCell.Row
ActiveCell.Offset(1, 0).Select
GoTo 1:
End If
End If
End If
'places an x in column I to show store has been placed in grid
Sheets("Generate Chill Grid").Select
Range("I3").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = "x"
Next cStore
End With
End Sub
