Could this be done?

Shalbriri

Shalbriri

Board Regular
Joined
Nov 4, 2010
Messages
93
For now i have been working on a projekt. and i was wondering.
Since my work has mostly been a big bucket of Copy-Paste.
Since the Copy was the first segment/part( i hopefully use the right terms, my english is a lil rusty ) of the whole code. And from that i made 13 copy pastes and then changed the cell's number.

so my question would be, could it be done so that it copies the first part itself into specified cells if i know that the next cell that must contain the data is 50 cell's away ?

i hope you guys understand. if not then i could upload the work to some place and you could see what i mean... well a part of it anyhow.
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes
Code: 
Private Sub CommandButton1_Click()
On Error Resume Next
Set ws = Worksheets("ribakardin")
Set wsKu = Worksheets("Kulu arvestus")
Application.Run "Sheet1.nim1"
Worksheets("Kulu arvestus").Range("a5", "d32").ClearContents
Worksheets("Kulu arvestus").Range("a23").Formula = "=vLOOKUP(Ribakardin!F11,Sheet1!A150:c192,3)"
Worksheets("kulu arvestus").Range("a22").Formula = "=vLookup(Ribakardin!i11,Sheet1!a195:c208,3)"
Worksheets("Kulu arvestus").Range("a24").Formula = "=vlookup(ribakardin!f11,Sheet1!a22:c63,3)"
Worksheets("kulu arvestus").Range("a25").Formula = "=vLOOKUP(Ribakardin!e11,Sheet1!a2:e19,5)"
Worksheets("Kulu arvestus").Range("a26").Formula = "=vlookup(Ribakardin!e11,sheet1!a2:c19,3)"
Worksheets("Kulu arvestuS").Range("a27").Formula = "=vlookup(Ribakardin!b11,Sheet1!a66:c148,3)"
Worksheets("kulu arvestus").Range("a14,a64,a114,h14,h64,h114,p14,p64,p114,x14,x64,x114,af14,af64").Font.Color = RGB(0, 0, 0)
Select Case LCase(Sheets("ribakardin").Range("j11").Value)
Case "1", "2", "3", "4", "5", "6"
Application.Run "Sheet1.magnet"
End Select
Select Case LCase(Sheets("Ribakardin").Range("D11").Value)
    Case "25mm", "25", "25 mm"
Application.Run "Sheet1.mingi"
Worksheets("Kulu arvestus").Range("b5").Value = "Aksel 4mm D-kujuline"
Worksheets("Kulu arvestus").Range("b6").Value = "Laagripukk rulliga"
Worksheets("Kulu arvestus").Range("b7").Value = "Rull valge"
Worksheets("Kulu arvestus").Range("b8").Value = "Rull must"
Worksheets("Kulu arvestus").Range("b9").Value = "Alaliistu otsik"
Worksheets("Kulu arvestus").Range("b11").Value = "Ülemise riba klamber"
Worksheets("Kulu arvestus").Range("b12").Value = "Akrüülpulga konks"
Worksheets("Kulu arvestus").Range("b13").Value = "Akrüülpulga otsik"
Worksheets("Kulu arvestus").Range("b14").Value = "Reduktor "
Worksheets("Kulu arvestus").Range("B15").Value = "Nöörilukk"
Worksheets("Kulu arvestus").Range("b16").Value = "Kelluke"
Worksheets("Kulu arvestus").Range("b17").Value = "Ülemise karbi otsik"
Worksheets("Kulu arvestus").Range("b18").Value = "põhjanupp"
Worksheets("Kulu arvestus").Range("B19").Value = "Nööriankur"
Worksheets("Kulu arvestus").Range("b20").Value = "Nöörijuhik"
Worksheets("Kulu arvestus").Range("B21").Value = "seadetross"
Worksheets("Kulu arvestus").Range("b23").Value = "Nöör 1,4mm"
Worksheets("Kulu arvestus").Range("b24").Value = "redel " & ws.Range("d11").Text + "mm"
Worksheets("Kulu arvestus").Range("B25").Value = "teras 42mm"
Worksheets("Kulu arvestus").Range("b26").Value = "teras 72mm"
Worksheets("Kulu arvestus").Range("b27").Value = "Ribi " + Worksheets("Ribakardin").Range("D11").Text + "mm"
Worksheets("Kulu arvestus").Range("c27").Value = (((Worksheets("Ribakardin").Range("p11").Value / 100) / 0.0214) * (Worksheets("Ribakardin").Range("o11").Value / 100)) * ws.Range("M11").Value
Worksheets("Kulu arvestus").Range("a5").Value = "125150"
Worksheets("Kulu arvestus").Range("a6").Value = "125200"
Worksheets("Kulu arvestus").Range("a7").Value = "125210"
Worksheets("Kulu arvestus").Range("a8").Value = "125220"
Worksheets("Kulu arvestus").Range("a9").Value = "125910"
Worksheets("Kulu arvestus").Range("a10").Value = "1253" + Worksheets("Ribakardin").Range("c11").Text
Worksheets("Kulu arvestus").Range("a11").Value = "125170A"
Worksheets("Kulu arvestus").Range("a12").Value = "125720"
Worksheets("Kulu arvestus").Range("a13").Value = "125710"
Worksheets("Kulu arvestus").Range("a14").Value = "125740"
Worksheets("Kulu arvestus").Range("a15").Value = "125750"
Worksheets("Kulu arvestus").Range("a16").Value = "125830"
Worksheets("Kulu arvestus").Range("a17").Value = "125160"
Worksheets("Kulu arvestus").Range("a18").Value = "125900"
Worksheets("Kulu arvestus").Range("a19").Value = "125820B"
Worksheets("Kulu arvestus").Range("a20").Value = "125810B"
Worksheets("Kulu arvestus").Range("a21").Value = "1254020B"
 End Select
 
Worksheets("Kulu arvestus").Range("B22").Value = "Seadehoob " + Worksheets("Ribakardin").Range("i11").Text
Worksheets("Kulu arvestus").Range("B10").Value = "Kinnitus " + Worksheets("Ribakardin").Range("C11").Text

If UCase(Left(ws.Range("c11").Value, 1)) >= "V" Then
Application.Run "Sheet1.V1"
Application.Run "Sheet1.Vk_1"
End If
If UCase(Left(Worksheets("Ribakardin").Range("C11").Value, 1)) <= "P" Then
Application.Run "Sheet1.P1"
ElseIf UCase(Left(Worksheets("Ribakardin").Range("C11").Value, 2)) >= "V6" Then
Application.Run "Sheet1.V1"
Application.Run "Sheet1.V6_1"
End If

 Select Case LCase(Sheets("Ribakardin").Range("d11").Value)
 Case "35mm", "35", "35 mm"
Application.Run "Sheet1.mingi2"
Application.Run "Sheet1.mm35_1"
End Select
Select Case LCase(Sheets("Ribakardin").Range("d11").Value)
Case "50", "50mm", "50 mm"
Application.Run "Sheet1.mingi2"
Application.Run "Sheet1.mm50_1"
End Select



Select Case LCase(Sheets("ribakardin").Range("a11").Value)
Case "PSK", "psk"
wsKu.Range("a19").Value = "135830W01"
wsKu.Range("b19").Value = "Puit" & wsKu.Range("b19").Text
wsKu.Range("c19").Value = 3 * ws.Range("m11").Value
Worksheets("Kulu arvestus").Range("b22").Value = "Puidust seadehoob " + Worksheets("ribakardin").Range("i11").Text
If wsKu.Range("a22").Value <> " " Then
wsKu.Range("b22").Value = ""
End If
Worksheets("Kulu arvestus").Range("b25").Value = "Puidust alaliist"
Worksheets("Kulu arvestus").Range("a25").Value = "1" + Worksheets("Ribakardin").Range("d11").Text + "WB" + Right(Worksheets("Ribakardin").Range("b11").Value, 2)
   If ws.Range("D11").Value = "25" Then
    If Right(Worksheets("Ribakardin").Range("b11").Value, 2) = "01" Then
Worksheets("Kulu arvestus").Range("a22").Value = "1255100W01"
ElseIf Right(Worksheets("Ribakardin").Range("b11").Value, 2) = "02" Then
Worksheets("Kulu arvestus").Range("a22").Value = "1255100W02"
ElseIf Right(Worksheets("Ribakardin").Range("b11").Value, 2) = "03" Then
Worksheets("kulu arvestus").Range("a22").Value = "1255100W03"
ElseIf Right(Worksheets("Ribakardin").Range("b11").Value, 2) = "04" Then
Worksheets("kulu arvestus").Range("a22").Value = "1255100W04"
ElseIf Right(Worksheets("Ribakardin").Range("b11").Value, 2) = "05" Then
Worksheets("Kulu arvestus").Range("a22").Value = "1255100W05"
ElseIf Right(Worksheets("Ribakardin").Range("b11").Value, 2) = "06" Then
Worksheets("Kulu arvestus").Range("a22").Value = "1255100W06"
End If
End If
If Right(Worksheets("Ribakardin").Range("e11").Value, 2) = "01" Then
Worksheets("Kulu arvestus").Range("a26").Value = "1X72S002"
ElseIf Right(Worksheets("Ribakardin").Range("e11").Value, 2) = "02" Then
Worksheets("Kulu arvestus").Range("a26").Value = "127S005"
ElseIf Right(Worksheets("Ribakardin").Range("e11").Value, 2) = "03" Then
Worksheets("Kulu arvestus").Range("A26").Value = "127S064"
End If
'Worksheets("kulu arvestus").Range("A9:D9").ClearContents
Worksheets("Kulu arvestus").Range("A12:D13").ClearContents
End Select

Select Case LCase(Sheets("Ribakardin").Range("A11").Value)
Case "INT", "int", "Int"
Application.Run "Sheet1.INT1"
End Select

This would be the first part of the code.
As you can see, it takes values from another sheet, so the right data would appear on the other one.
 
Upvote 0
I am not sure how the code you provided in post #3 works with your question in post #1:

"... could it be done so that it copies the first part itself into specified cells if i know that the next cell that must contain the data is 50 cell's away ?"

You are writing data to a worksheet based on inputs from another worksheet. I am not sure what you want your code to do. that it is not already doing.
 
Upvote 0
the code itself works perfectly, but im just curiouse could it be modified that i dont have 12 other copies of the same text with slightly different values .
 
Upvote 0
What elements in your code would be different for another copy? Please repost the code in #3 with the elements that would change colored red.
 
Upvote 0
Shalbriri said:
Code: 
Private Sub CommandButton1_Click()
On Error Resume Next
Set ws = Worksheets("ribakardin")
Set wsKu = Worksheets("Kulu arvestus")
Application.Run "Sheet1.nim1"
[COLOR=Black]Worksheets("Kulu arvestus").Range("[COLOR=Red]a5", "d32[/COLOR]").ClearContents
[/COLOR] [COLOR=Black]Worksheets("Kulu arvestus").Range("[COLOR=Red]a23[/COLOR]").Formula = "=vLOOKUP(Ribakardin![COLOR=Red]F11[/COLOR],Sheet1!A150:c192,3)"
Worksheets("kulu arvestus").Range("[COLOR=Red]a22[/COLOR]").Formula = "=vLookup(Ribakardin![COLOR=Red]i11[/COLOR],Sheet1!a195:c208,3)"
Worksheets("Kulu arvestus").Range("[COLOR=Red]a24[/COLOR]").Formula = "=vlookup(ribakardin![COLOR=Red]f11[/COLOR],Sheet1!a22:c63,3)"
Worksheets("kulu arvestus").Range("[COLOR=Red]a25[/COLOR]").Formula = "=vLOOKUP(Ribakardin![COLOR=Red]e11[/COLOR],Sheet1!a2:e19,5)"
Worksheets("Kulu arvestus").Range("[COLOR=Red]a26[/COLOR]").Formula = "=vlookup(Ribakardin![COLOR=Red]e11[/COLOR],sheet1!a2:c19,3)"
Worksheets("Kulu arvestuS").Range("[COLOR=Red]a27[/COLOR]").Formula = "=vlookup(Ribakardin![COLOR=Red]b11[/COLOR],Sheet1!a66:c148,3)"[/COLOR]
Worksheets("kulu arvestus").Range("a14,a64,a114,h14,h64,h114,p14,p64,p114,x14,x64,x114,af14,af64").Font.Color = RGB(0, 0, 0)
Select Case LCase(Sheets("ribakardin").Range("[COLOR=Red]j11[/COLOR]").Value)
Case "1", "2", "3", "4", "5", "6"
Application.Run "Sheet1.magnet"
End Select
Select Case LCase(Sheets("Ribakardin").Range("[COLOR=Red]D11[/COLOR]").Value)
    Case "25mm", "25", "25 mm"
Application.Run "Sheet1.mingi"
Worksheets("Kulu arvestus").Range("[COLOR=Red]b5[/COLOR]").Value = "Aksel 4mm D-kujuline"
Worksheets("Kulu arvestus").Range("[COLOR=Red]b6[/COLOR]").Value = "Laagripukk rulliga"
Worksheets("Kulu arvestus").Range("[COLOR=Red]b7[/COLOR]").Value = "Rull valge"
Worksheets("Kulu arvestus").Range("[COLOR=Red]b8[/COLOR]").Value = "Rull must"
Worksheets("Kulu arvestus").Range("[COLOR=Red]b9[/COLOR]").Value = "Alaliistu otsik"
Worksheets("Kulu arvestus").Range("[COLOR=Red]b11[/COLOR]").Value = "Ülemise riba klamber"
Worksheets("Kulu arvestus").Range("[COLOR=Red]b12[/COLOR]").Value = "Akrüülpulga konks"
Worksheets("Kulu arvestus").Range("[COLOR=Red]b13[/COLOR]").Value = "Akrüülpulga otsik"
Worksheets("Kulu arvestus").Range("[COLOR=Red]b14[/COLOR]").Value = "Reduktor "
Worksheets("Kulu arvestus").Range("[COLOR=Red]B15[/COLOR]").Value = "Nöörilukk"
Worksheets("Kulu arvestus").Range("[COLOR=Red]b16[/COLOR]").Value = "Kelluke"
Worksheets("Kulu arvestus").Range("[COLOR=Red]b17[/COLOR]").Value = "Ülemise karbi otsik"
Worksheets("Kulu arvestus").Range("[COLOR=Red]b18[/COLOR]").Value = "põhjanupp"
Worksheets("Kulu arvestus").Range("[COLOR=Red]B19[/COLOR]").Value = "Nööriankur"
Worksheets("Kulu arvestus").Range("[COLOR=Red]b20[/COLOR]").Value = "Nöörijuhik"
Worksheets("Kulu arvestus").Range("[COLOR=Red]B21[/COLOR]").Value = "seadetross"
Worksheets("Kulu arvestus").Range("[COLOR=Red]b23[/COLOR]").Value = "Nöör 1,4mm"
Worksheets("Kulu arvestus").Range("[COLOR=Red]b24[/COLOR]").Value = "redel " & ws.Range("[COLOR=Red]d11[/COLOR]").Text + "mm"
Worksheets("Kulu arvestus").Range("[COLOR=Red]B25[/COLOR]").Value = "teras 42mm"
Worksheets("Kulu arvestus").Range("[COLOR=Red]b26[/COLOR]").Value = "teras 72mm"
Worksheets("Kulu arvestus").Range("[COLOR=Red]b27[/COLOR]").Value = "Ribi " + Worksheets("Ribakardin").Range("[COLOR=Red]D11[/COLOR]").Text + "mm"
Worksheets("Kulu arvestus").Range("[COLOR=Red]c27[/COLOR]").Value = (((Worksheets("Ribakardin").Range("[COLOR=Red]p11[/COLOR]").Value / 100) / 0.0214) * (Worksheets("Ribakardin").Range("[COLOR=Red]o11[/COLOR]").Value / 100)) * ws.Range("[COLOR=Red]M11[/COLOR]").Value
Worksheets("Kulu arvestus").Range("[COLOR=Red]a5[/COLOR]").Value = "125150"
Worksheets("Kulu arvestus").Range("[COLOR=Red]a6[/COLOR]").Value = "125200"
Worksheets("Kulu arvestus").Range("[COLOR=Red]a7[/COLOR]").Value = "125210"
Worksheets("Kulu arvestus").Range("[COLOR=Red]a8"[/COLOR]).Value = "125220"
Worksheets("Kulu arvestus").Range("[COLOR=Red]a9[/COLOR]").Value = "125910"
Worksheets("Kulu arvestus").Range("[COLOR=Red]a10[/COLOR]").Value = "1253" + Worksheets("Ribakardin").Range("[COLOR=Red]c11[/COLOR]").Text
Worksheets("Kulu arvestus").Range("[COLOR=Red]a11[/COLOR]").Value = "125170A"
Worksheets("Kulu arvestus").Range("[COLOR=Red]a12[/COLOR]").Value = "125720"
Worksheets("Kulu arvestus").Range("[COLOR=Red]a13[/COLOR]").Value = "125710"
Worksheets("Kulu arvestus").Range("[COLOR=Red]a14[/COLOR]").Value = "125740"
Worksheets("Kulu arvestus").Range("[COLOR=Red]a15[/COLOR]").Value = "125750"
Worksheets("Kulu arvestus").Range("[COLOR=Red]a16[/COLOR]").Value = "125830"
Worksheets("Kulu arvestus").Range("[COLOR=Red]a17[/COLOR]").Value = "125160"
Worksheets("Kulu arvestus").Range("[COLOR=Red]a18[/COLOR]").Value = "125900"
Worksheets("Kulu arvestus").Range("[COLOR=Red]a19[/COLOR]").Value = "125820B"
Worksheets("Kulu arvestus").Range("[COLOR=Red]a20[/COLOR]").Value = "125810B"
Worksheets("Kulu arvestus").Range("[COLOR=Red]a21[/COLOR]").Value = "1254020B"
 End Select
 
Worksheets("Kulu arvestus").Range("[COLOR=Red]B22[/COLOR]").Value = "Seadehoob " + Worksheets("Ribakardin").Range("[COLOR=Red]i11[/COLOR]").Text
Worksheets("Kulu arvestus").Range("[COLOR=Red]B10[/COLOR]").Value = "Kinnitus " + Worksheets("Ribakardin").Range("[COLOR=Red]C11[/COLOR]").Text

If UCase(Left(ws.Range("[COLOR=Red]c11[/COLOR]").Value, 1)) >= "V" Then
Application.Run "Sheet1.V1"
Application.Run "Sheet1.Vk_1"
End If
If UCase(Left(Worksheets("Ribakardin").Range("[COLOR=Red]C11[/COLOR]").Value, 1)) <= "P" Then
Application.Run "Sheet1.P1"
ElseIf UCase(Left(Worksheets("Ribakardin").Range("[COLOR=Red]C11[/COLOR]").Value, 2)) >= "V6" Then
Application.Run "Sheet1.V1"
Application.Run "Sheet1.V6_1"
End If

 Select Case LCase(Sheets("Ribakardin").Range("[COLOR=Red]d11[/COLOR]").Value)
 Case "35mm", "35", "35 mm"
Application.Run "Sheet1.mingi2"
Application.Run "Sheet1.mm35_1"
End Select
Select Case LCase(Sheets("Ribakardin").Range("[COLOR=Red]d11[/COLOR]").Value)
Case "50", "50mm", "50 mm"
Application.Run "Sheet1.mingi2"
Application.Run "Sheet1.mm50_1"
End Select



Select Case LCase(Sheets("ribakardin").Range("[COLOR=Red]a11[/COLOR]").Value)
Case "PSK", "psk"
wsKu.Range("[COLOR=Red]a19[/COLOR]").Value = "135830W01"
wsKu.Range("[COLOR=Red]b19[/COLOR]").Value = "Puit" & wsKu.Range("b[COLOR=Red]19[/COLOR]").Text
wsKu.Range("[COLOR=Red]c19[/COLOR]").Value = 3 * ws.Range("m[COLOR=Red]11[/COLOR]").Value
Worksheets("Kulu arvestus").Range("[COLOR=Red]b22[/COLOR]").Value = "Puidust seadehoob " + Worksheets("ribakardin").Range("[COLOR=Red]i11[/COLOR]").Text
If wsKu.Range("[COLOR=Red]a22[/COLOR]").Value <> " " Then
wsKu.Range("[COLOR=Red]b22[/COLOR]").Value = ""
End If
Worksheets("Kulu arvestus").Range("[COLOR=Red]b25[/COLOR]").Value = "Puidust alaliist"
Worksheets("Kulu arvestus").Range("[COLOR=Red]a25[/COLOR]").Value = "1" + Worksheets("Ribakardin").Range("[COLOR=Red]d11[/COLOR]").Text + "WB" + Right(Worksheets("Ribakardin").Range("[COLOR=Red]b11[/COLOR]").Value, 2)
   If ws.Range("[COLOR=Red]D11[/COLOR]").Value = "25" Then
    If Right(Worksheets("Ribakardin").Range("[COLOR=Red]b11[/COLOR]").Value, 2) = "01" Then
Worksheets("Kulu arvestus").Range("[COLOR=Red]a22[/COLOR]").Value = "1255100W01"
ElseIf Right(Worksheets("Ribakardin").Range("[COLOR=Red]b11[/COLOR]").Value, 2) = "02" Then
Worksheets("Kulu arvestus").Range("[COLOR=Red]a22[/COLOR]").Value = "1255100W02"
ElseIf Right(Worksheets("Ribakardin").Range("[COLOR=Red]b11[/COLOR]").Value, 2) = "03" Then
Worksheets("kulu arvestus").Range("[COLOR=Red]a22[/COLOR]").Value = "1255100W03"
ElseIf Right(Worksheets("Ribakardin").Range("[COLOR=Red]b11[/COLOR]").Value, 2) = "04" Then
Worksheets("kulu arvestus").Range("[COLOR=Red]a22[/COLOR]").Value = "1255100W04"
ElseIf Right(Worksheets("Ribakardin").Range("[COLOR=Red]b11[/COLOR]").Value, 2) = "05" Then
Worksheets("Kulu arvestus").Range("[COLOR=Red]a22[/COLOR]").Value = "1255100W05"
ElseIf Right(Worksheets("Ribakardin").Range("[COLOR=Red]b11[/COLOR]").Value, 2) = "06" Then
Worksheets("Kulu arvestus").Range("[COLOR=Red]a22[/COLOR]").Value = "1255100W06"
End If
End If
If Right(Worksheets("Ribakardin").Range("[COLOR=Red]e11[/COLOR]").Value, 2) = "01" Then
Worksheets("Kulu arvestus").Range("[COLOR=Red]a26[/COLOR]").Value = "1X72S002"
ElseIf Right(Worksheets("Ribakardin").Range("[COLOR=Red]e11[/COLOR]").Value, 2) = "02" Then
Worksheets("Kulu arvestus").Range("[COLOR=Red]a26[/COLOR]").Value = "127S005"
ElseIf Right(Worksheets("Ribakardin").Range("[COLOR=Red]e11[/COLOR]").Value, 2) = "03" Then
Worksheets("Kulu arvestus").Range("[COLOR=Red]A26[/COLOR]").Value = "127S064"
End If
'Worksheets("kulu arvestus").Range("[COLOR=Red]A9:D9[/COLOR]").ClearContents
Worksheets("Kulu arvestus").Range("[COLOR=Red]A12:D13[/COLOR]").ClearContents
End Select

Select Case LCase(Sheets("Ribakardin").Range("[COLOR=Red]A11[/COLOR]").Value)
Case "INT", "int", "Int"
Application.Run "Sheet1.INT1"
End Select
This would be the first part of the code.
As you can see, it takes values from another sheet, so the right data would appear on the other one.
Click to expand...

On sheet Ribakardin the cell location jumps +1 exmp: A11 to T11 next A12 to T12

On Sheet Kulu arvestus the cell loc. jumps +50 exmp: A5 to A27(Ribakardin A11 data), A55 to A77( Ribakardin A12 data)

EDIT:
Oh yeah and under Application.run there are also procedures to complete the right list of items.
 
Last edited:
Upvote 0
I made and highlighted the required changes to parts of your code. This should serve as a model for how to change the rest. Please let me know if you have any questsions.

Rich (BB code): 
Private Sub CommandButton1_Click()
    On Error Resume Next '<= generally not advisable for the whole procedure since it will mask all errors.
                         '<= it should be used and then reset with 'On Error Goto 0'
                         '<= around the portions of the code that require it.
 
   Dim iWsRiOffset As Integer
   Dim iWsKuOffset As Integer
   Dim iLoop As Integer
 
   iWsRiOffset = 0
   iWsKuOffset = 0
 
   Do While iLoop < 14
 
        Set ws = Worksheets("ribakardin")
        Set wsku = Worksheets("Kulu arvestus")
 
        Application.Run "Sheet1.nim1"
        With wsku
            .Cells(5, 1 + iWsKuOffset).ClearContents
            .Cells(32, 4 + iWsKuOffset).ClearContents
            .Cells(23, 1 + iWsKuOffset).Formula = "=vLOOKUP(Ribakardin!F" & 11 + iWsRiOffset & ",Sheet1!A150:c192,3)"
            .Cells(22, 1 + iWsKuOffset).Formula = "=vLookup(Ribakardin!i" & 11 + iWsRiOffset & ",Sheet1!a195:c208,3)"
            .Cells(24, 1 + iWsKuOffset).Formula = "=vlookup(ribakardin!f" & 11 + iWsRiOffset & ",Sheet1!a22:c63,3)"
            .Cells(25, 1 + iWsKuOffset).Formula = "=vLOOKUP(Ribakardin!e" & 11 + iWsRiOffset & ",Sheet1!a2:e19,5)"
            .Cells(26, 1 + iWsKuOffset).Formula = "=vlookup(Ribakardin!e" & 11 + iWsRiOffset & ",sheet1!a2:c19,3)"
           .Cells(27, 1 + iWsKuOffset).Formula = "=vlookup(Ribakardin!b" & 11 + iWsRiOffset & ",Sheet1!a66:c148,3)"
            .Range("a14,a64,a114,h14,h64,h114,p14,p64,p114,x14,x64,x114,af14,af64").Font.Color = RGB(0, 0, 0)
        End With
 
        Select Case LCase(Sheets("ribakardin").Cells(11, 10 + iWsRiOffset).Value)
        Case "1", "2", "3", "4", "5", "6"
            Application.Run "Sheet1.magnet"
        End Select
 
        Select Case LCase(Sheets("Ribakardin").Range("D11").Value)
        Case "25mm", "25", "25 mm"
            Application.Run "Sheet1.mingi"
            With wsku
                .Range("b5").Value = "Aksel 4mm D-kujuline"
                .Range("b6").Value = "Laagripukk rulliga"
                .Range("b7").Value = "Rull valge"
                .Range("b8").Value = "Rull must"
                .Range("b9").Value = "Alaliistu otsik"
                .Range("b11").Value = "Ülemise riba klamber"
                .Range("b12").Value = "Akrüülpulga konks"
                .Range("b13").Value = "Akrüülpulga otsik"
                .Range("b14").Value = "Reduktor "
                .Range("B15").Value = "Nöörilukk"
                .Range("b16").Value = "Kelluke"
                .Range("b17").Value = "Ülemise karbi otsik"
                .Range("b18").Value = "põhjanupp"
                .Range("B19").Value = "Nööriankur"
                .Range("b20").Value = "Nöörijuhik"
                .Range("B21").Value = "seadetross"
                .Range("b23").Value = "Nöör 1,4mm"
                .Range("b24").Value = "redel " & ws.Range("d11").Text + "mm"
                .Range("B25").Value = "teras 42mm"
                .Range("b26").Value = "teras 72mm"
                .Range("b27").Value = "Ribi " + Worksheets("Ribakardin").Range("D11").Text + "mm"
                .Range("c27").Value = (((Worksheets("Ribakardin").Range("p11").Value / 100) / 0.0214) * (Worksheets("Ribakardin").Range("o11").Value / 100)) * ws.Range("M11").Value
                .Range("a5").Value = "125150"
                .Range("a6").Value = "125200"
                .Range("a7").Value = "125210"
                .Range("a8").Value = "125220"
                .Range("a9").Value = "125910"
                .Range("a10").Value = "1253" + Worksheets("Ribakardin").Range("c11").Text
                .Range("a11").Value = "125170A"
                .Range("a12").Value = "125720"
                .Range("a13").Value = "125710"
                .Range("a14").Value = "125740"
                .Range("a15").Value = "125750"
                .Range("a16").Value = "125830"
                .Range("a17").Value = "125160"
                .Range("a18").Value = "125900"
                .Range("a19").Value = "125820B"
                .Range("a20").Value = "125810B"
                .Range("a21").Value = "1254020B"
            End With
         End Select
 
        Worksheets("Kulu arvestus").Range("B22").Value = "Seadehoob " + Worksheets("Ribakardin").Range("i11").Text
        Worksheets("Kulu arvestus").Range("B10").Value = "Kinnitus " + Worksheets("Ribakardin").Range("C11").Text
 
        If UCase(Left(ws.Range("c11").Value, 1)) >= "V" Then
            Application.Run "Sheet1.V1"
            Application.Run "Sheet1.Vk_1"
        End If
 
        If UCase(Left(Worksheets("Ribakardin").Range("C11").Value, 1)) <= "P" Then
            Application.Run "Sheet1.P1"
        ElseIf UCase(Left(Worksheets("Ribakardin").Range("C11").Value, 2)) >= "V6" Then
            Application.Run "Sheet1.V1"
            Application.Run "Sheet1.V6_1"
        End If
 
        Select Case LCase(Sheets("Ribakardin").Range("d11").Value)
        Case "35mm", "35", "35 mm"
            Application.Run "Sheet1.mingi2"
            Application.Run "Sheet1.mm35_1"
        End Select
 
        Select Case LCase(Sheets("Ribakardin").Range("d11").Value)
        Case "50", "50mm", "50 mm"
            Application.Run "Sheet1.mingi2"
            Application.Run "Sheet1.mm50_1"
        End Select
 
        Select Case LCase(Sheets("ribakardin").Range("a11").Value)
        Case "PSK", "psk"
            wsku.Range("a19").Value = "135830W01"
            wsku.Range("b19").Value = "Puit" & wsku.Range("b19").Text
            wsku.Range("c19").Value = 3 * ws.Range("m11").Value
            Worksheets("Kulu arvestus").Range("b22").Value = _
                "Puidust seadehoob " + Worksheets("ribakardin").Range("i11").Text
            If wsku.Range("a22").Value <> " " Then
                wsku.Range("b22").Value = ""
            End If
            Worksheets("Kulu arvestus").Range("b25").Value = "Puidust alaliist"
            Worksheets("Kulu arvestus").Range("a25").Value = _
                "1" + Worksheets("Ribakardin").Range("d11").Text + "WB" + _
                Right(Worksheets("Ribakardin").Range("b11").Value, 2)
            If ws.Range("D11").Value = "25" Then
                If Right(Worksheets("Ribakardin").Range("b11").Value, 2) = "01" Then
                    Worksheets("Kulu arvestus").Range("a22").Value = "1255100W01"
                ElseIf Right(Worksheets("Ribakardin").Range("b11").Value, 2) = "02" Then
                    Worksheets("Kulu arvestus").Range("a22").Value = "1255100W02"
                ElseIf Right(Worksheets("Ribakardin").Range("b11").Value, 2) = "03" Then
                    Worksheets("kulu arvestus").Range("a22").Value = "1255100W03"
                ElseIf Right(Worksheets("Ribakardin").Range("b11").Value, 2) = "04" Then
                    Worksheets("kulu arvestus").Range("a22").Value = "1255100W04"
                ElseIf Right(Worksheets("Ribakardin").Range("b11").Value, 2) = "05" Then
                    Worksheets("Kulu arvestus").Range("a22").Value = "1255100W05"
                ElseIf Right(Worksheets("Ribakardin").Range("b11").Value, 2) = "06" Then
                    Worksheets("Kulu arvestus").Range("a22").Value = "1255100W06"
                End If
            End If
 
            If Right(Worksheets("Ribakardin").Range("e11").Value, 2) = "01" Then
                Worksheets("Kulu arvestus").Range("a26").Value = "1X72S002"
            ElseIf Right(Worksheets("Ribakardin").Range("e11").Value, 2) = "02" Then
                Worksheets("Kulu arvestus").Range("a26").Value = "127S005"
            ElseIf Right(Worksheets("Ribakardin").Range("e11").Value, 2) = "03" Then
                Worksheets("Kulu arvestus").Range("A26").Value = "127S064"
            End If
            'Worksheets("kulu arvestus").Range("A9:D9").ClearContents
            Worksheets("Kulu arvestus").Range("A12:D13").ClearContents
        End Select
 
        Select Case LCase(Sheets("Ribakardin").Range("A11").Value)
        Case "INT", "int", "Int"
            Application.Run "Sheet1.INT1"
        End Select
 
 
 
       iWsRiOffset = iWsRiOffset + 1
       iWsKuOffset = iWsKuOffset + 50
       iLoop = iLoop + 1
   Loop
 
 
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Open, Copy data and Close a workbook based on the names of the tabs in my active workbook
Replies
2
Views
106
Aurore87
A
R
  • Solved
Character count on each line within one cell
3 4 5
Replies
40
Views
783
Peter_SSs
Peter_SSs
W
  • Question Question
Macro/VBA code code for creating new excel file for each pivot table from a column filter
Replies
1
Views
483
Anthony47
A
Z
  • Question Question
VBA Complex Organizational Loop
Replies
1
Views
48
HighAndWilder
H
B
  • Question Question
Removed Feature: Pivot Table
Replies
0
Views
176
bec98
B

Forum statistics

Threads
1,224,607
Messages
6,179,871
Members
452,948
Latest member
UsmanAli786

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top