0

I'm trying to use a Select Case to log production time-tables. I've tried a couple angles - one being Select Case. Every time I run the Macro I get the default ERROR "Wrong Model Entered...."

Private Sub Transfer()

Dim ModR As String
Dim Count1 As Integer
Dim Count2 As Integer
Dim Count3 As Integer
Dim Count4 As Integer
Dim Count5 As Integer

Count1 = 0
Count2 = 0
Count3 = 0
Count4 = 0
Count5 = 0

ModR = Worksheets(1).Range("B3").Value

Select Case ModR
  Case "Model 1"
    Worksheets(1).Range("B4:B6").Copy
    Worksheets(2).Range("B3:B5").Offset(Count1, 0).PasteSpecial
    Count1 = Count1 + 1
  Case "Model 2"
    Worksheets(1).Range("B4:B6").Copy
    Worksheets(2).Range("B7:B9").Offset(Count2, 0).PasteSpecial
    Count2 = Count2 + 1
  Case "Mode1 3"
    Worksheets(1).Range("B4:B6").Copy
    Worksheets(2).Range("B11:B13").Offset(Count3, 0).PasteSpecial
    Count3 = Count3 + 1
  Case "Model 4"
    Worksheets(1).Range("B4:B6").Copy
    Worksheets(2).Range("B15:B17").Offset(Count4, 0).PasteSpecial
    Count4 = Count4 + 1
  Case "Model 5"
    Worksheets(1).Range("B4:B6").Copy
    Worksheets(2).Range("B19:B21").Offset(Count5, 0).PasteSpecial
    Count5 = Count5 + 1
  Case Else
    MsgBox "Wrong Model Entered / Model Does Not Exist"
  End Select
End Sub

Public Sub call_Transfer()
  Call Transfer
End Sub

I'm thinking the problem lies within 

ModR = Worksheets(1).Range("B3").Value
Community
  • 1
  • 1
newtons003
  • 7
  • 6
  • What is the value that's entered in the worksheet with the index of 1 (not necessarily the sheet named `Sheet1`) cell `B3`? Also what is the count being used for? – Gareth May 21 '14 at 12:38
  • I would like to enter model info into sheet1, fields B3:B6. B3 contains the name of the model, of which, I would like to search for (ColumnA) on sheet2. Once it's located on sheet2, copy the fields B4:B6 from sheet1 into the fields B3:B5 (depending on the model entered B3:B5 will change so for model2 it would copy to B7:B9) of sheet2 then increment and wait for the next entry. The count is being used to serve as a place-holder for the increment. – newtons003 May 21 '14 at 12:44
  • How should I approach incrementing the fields on worksheet2? I'm trying to create an average production time per step of each model... – newtons003 May 21 '14 at 12:49
  • It's copying to B3:B5 - but it won't increment. Also, for some reason, Model3 is giving me an error. All the rest will copy to their "prospective" positions. – newtons003 May 21 '14 at 12:55

1 Answers1

0

In order for the increment to work, you have to use global variables. Also, your offset leads to the cells being shifted downwards instead of to the right. I altered your code. It will increment the offset and shift it to the right on new macro executions.

Dim Count1 As Integer
Dim Count2 As Integer
Dim Count3 As Integer
Dim Count4 As Integer
Dim Count5 As Integer

Private Sub Transfer()

Dim ModR As String

ModR = Worksheets(1).Range("B3").Value

Select Case ModR
  Case "Model 1"
    Worksheets(1).Range("B4:B6").Copy
    Worksheets(2).Range("B3:B5").Offset(0, Count1).PasteSpecial
    Count1 = Count1 + 1
  Case "Model 2"
    Worksheets(1).Range("B4:B6").Copy
    Worksheets(2).Range("B7:B9").Offset(0, Count2).PasteSpecial
    Count2 = Count2 + 1
  Case "Model 3"
    Worksheets(1).Range("B4:B6").Copy
    Worksheets(2).Range("B11:B13").Offset(0, Count3).PasteSpecial
    Count3 = Count3 + 1
  Case "Model 4"
    Worksheets(1).Range("B4:B6").Copy
    Worksheets(2).Range("B15:B17").Offset(0, Count4).PasteSpecial
    Count4 = Count4 + 1
  Case "Model 5"
    Worksheets(1).Range("B4:B6").Copy
    Worksheets(2).Range("B19:B21").Offset(0, Count5).PasteSpecial
    Count5 = Count5 + 1
  Case Else
    MsgBox "Wrong Model Entered / Model Does Not Exist"
  End Select
End Sub

Public Sub call_Transfer()
  Call Transfer
End Sub

Just paste this in your Module. I hope it's doing what you were looking for.

EngJon
  • 987
  • 8
  • 20