rhino4eva
Active Member
- Joined
- Apr 1, 2009
- Messages
- 262
- Office Version
- 2010
- Platform
- Windows
Sub SampleEngine(sPlateNum As String, sRespRange As String, sLoop As Integer)
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Dim rFound As Range
Set WS1 = Sheets("DATA")
Set WS2 = Sheets(sPlateNum)
SET WS3= Sheets("BPP")
WS2.Range(sRespRange).ClearContents
For X = 2 To sLoop
With WS2.Range(sRespRange)
On Error Resume Next
Set rFound = .Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns, LookIn:=xlValues)
On Error GoTo 0
If rFound Is Nothing Then
.Cells(1).Value = WS1.Range("C" & X).Value
ElseIf Intersect(rFound, .Cells(.Rows.Count, .Columns.Count)) Is Nothing Then
If rFound.Row < .Rows(.Rows.Count).Row Then
rFound.Offset(1, 0).Value = WS1.Range("C" & X).Value
Else
rFound.Offset(-.Rows.Count + 1, 1).Value = WS1.Range("C" & X).Value
End If
End If
End With
Next X
End Sub
----------------------------------------------------------------
Sub fillsample24()
Sheets("1PLATE").Range("C4:N11").ClearContents
'orange samples
Call SampleEngine("1PLATE", "C4:h11", "24")
'yellow samples
Call SampleEngine("1PLATE", "f4:n11", "24")
'green samples
Call SampleEngine("1PLATE", "i4:k11", "24")
'blue samples
Call SampleEngine("1PLATE", "l4:n11", "24")
the basic code for this was kindly provided by a board member. I have developed it little further but I have hit the wall.
It basically scans thru a list on WS1 and copes it to the next available space in a zone on WS2
I need to insert an IF THEN statement if the string on WS1<> "BPP" copy WS2 and if it does copy to WS3
BUT he exact location the statement eludes me
Can anyone dissect this for me and make a suggestion
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Dim rFound As Range
Set WS1 = Sheets("DATA")
Set WS2 = Sheets(sPlateNum)
SET WS3= Sheets("BPP")
WS2.Range(sRespRange).ClearContents
For X = 2 To sLoop
With WS2.Range(sRespRange)
On Error Resume Next
Set rFound = .Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns, LookIn:=xlValues)
On Error GoTo 0
If rFound Is Nothing Then
.Cells(1).Value = WS1.Range("C" & X).Value
ElseIf Intersect(rFound, .Cells(.Rows.Count, .Columns.Count)) Is Nothing Then
If rFound.Row < .Rows(.Rows.Count).Row Then
rFound.Offset(1, 0).Value = WS1.Range("C" & X).Value
Else
rFound.Offset(-.Rows.Count + 1, 1).Value = WS1.Range("C" & X).Value
End If
End If
End With
Next X
End Sub
----------------------------------------------------------------
Sub fillsample24()
Sheets("1PLATE").Range("C4:N11").ClearContents
'orange samples
Call SampleEngine("1PLATE", "C4:h11", "24")
'yellow samples
Call SampleEngine("1PLATE", "f4:n11", "24")
'green samples
Call SampleEngine("1PLATE", "i4:k11", "24")
'blue samples
Call SampleEngine("1PLATE", "l4:n11", "24")
the basic code for this was kindly provided by a board member. I have developed it little further but I have hit the wall.
It basically scans thru a list on WS1 and copes it to the next available space in a zone on WS2
I need to insert an IF THEN statement if the string on WS1<> "BPP" copy WS2 and if it does copy to WS3
BUT he exact location the statement eludes me
Can anyone dissect this for me and make a suggestion