Conditional value selection , help needed

[Sekaran]

Hello Experts,

Below is my logic to copy/paste and sort data between two excel sheets.

Logic:

1. Want to copy A2 to D31 in XL “116132” to B16 of “2”
2. Want to copy I2 to I31 in XL “116132” to F16 of “2”

Till here we bring in all data inside XL we need

1. Then I need to sort the data in XL “2” based on the condition.
2. If Part number characters length is 10 then it should remain in its place
3. If Part number characters length is 8 then it should copy data in that row and paste it from row A48
4. If Part number characters length is 7 then it should copy data in that row and paste it from row A55

Have written my logics till here and Its not working, also need more coding in sorting the data

7. Deleting the empty row which got copied
8. Also need to write a sub logic where the data gets pasted in next lines for character if length is 8 & 6.

Below is my code:

Sub Engineering()

'If Length of coloumn B is 10
Dim Answer As VbMsgBoxResult
Answer = MsgBox("Are you sure to copy BOM contents?", vbYesNo, "Run Macro")
If Answer = vbYes Then
Workbooks("116132.xlsx").Worksheets("BOM").Range("A2:D31").Copy _
Workbooks("2.xlsx").Worksheets("MPL").Range("B16")
Workbooks("116132.xlsx").Worksheets("BOM").Range("I2:I31").Copy _
Workbooks("2.xlsx").Worksheets("MPL").Range("F16")
Dim Answer1 As Range
For Each Answer1 In Workbooks("2.xlsx").Worksheets("MPL").Range("B16:B45")
If Selection.Len = "10" Then
Next Answer1
If Selection.Len = "8" Then
Workbooks("2.xlsx").Worksheets("MPL").Rows(Selection).Copy _
Workbooks("2.xlsx").Worksheets("MPL").Range("A47")
Next Answer1
If Selection.Len = "6" Then
Workbooks("2.xlsx").Worksheets("MPL").Rows(Selection).Copy _
Workbooks("2.xlsx").Worksheets("MPL").Range("A55")
Next Answer1

End Sub

So basically need help
1. to understand the issue in my current code
2. How to increment the rows while pasting data for condition if character length = 8 & 6

Appreciate any help from this forum. Thanks

 

[Sekaran]

Thats the error Im getting while running current code.

 

[Zot]

Once you write your code in VB Editor, you can compile it (compile here is like checking for syntax error). Go to Debug>Compile VBAProject. It will tell you what's wrong in your code if not running. Right or wrong answer is different story

You can install XL2BB and select range to capture and paste here your sheet.

 

[Zot]

If you put in one line like this:
If Answer = vbYes Then wbsource.wssource.rangesource.Copy wbtarget.wstarget.rangetarget

then it is fine. If you put in multiple row, the syntax should be
If Answer = vbYes Then
wbsource.wssource.rangesource.Copy wbtarget.wstarget.rangetarget
End If

you need End If to close in case of several lines.

Each statement For need to close with Next. However here you have 1 For but multiple Next

Your code just not follow the coding rule

 

[Sekaran]

Hi Zot,

Thanks for your reply. Im a Mechanical Engineer trying to automate some of my regular repatitive workin excel.

Have very basic knowledge about VBA macros. Im interested in learing and correct my basics.

Would you kindly suggest any material or online source I can learn it right.

Thanks

 

[Zot]

Looks like you have 2 workbooks
1) Workbook named 116132. Workbook named 2.
2) the wb 116132 has sheet called BOM. wb 2 has sheet called MPL.

Your copy code from wb 116132 to wb 2 looks ok to me but the rest are wrongly written

 

[Zot]

Hi Zot,

Thanks for your reply. Im a Mechanical Engineer trying to automate some of my regular repatitive workin excel.

Have very basic knowledge about VBA macros. Im interested in learing and correct my basics.

Would you kindly suggest any material or online source I can learn it right.

Thanks

There are plenty free tutorial. I have nothing specific. I just Googled one here

What is VBA in Excel - The Best Way to Learn Programming in Excel

This is a complete tutorial to learn VBA in Excel. On this page, you will find more than 100 tutorials that make it the best way to learn VBA.

excelchamps.com

You can just Googled say like VBA If or VBA For, then you can see sample how they are written.

 

[Sekaran]

Great !! Yeah that seems to be a very detailed learning material. Thanks for forwarding it.

Just have a small question about the condition I'm using in my code above.

Is " If Selection.Len = "8" Then" this syntax correct for a condition if the selected cell character length is 8 ?

 

[Zot]

Great !! Yeah that seems to be a very detailed learning material. Thanks for forwarding it.

Just have a small question about the condition I'm using in my code above.

Is " If Selection.Len = "8" Then" this syntax correct for a condition if the selected cell character length is 8 ?

Not correct. It is Len(<expression>)

VBA LEN

LEN function is a common function for both worksheets and VBA. It is an inbuilt function for both platforms, and the syntax to use this function is

www.wallstreetmojo.com

 

[Zot]

Here is sample how you could develop the code for this task. I'm not sure if this work because not tested.

Sub Engineering()

'It is simpler to asign variable.
' In this case I wanted to put macro in workbook 116132 and named it wbSource
Dim rngSource As Range, rngDest As Range, cell As Range
Dim Fname As Variant
Dim rowLast As Long, n6 As Long, n8 As Long
Dim wbSource As Workbook, wbDest As Workbook
Dim wsSource As Worksheet, wsDest As Worksheet

' Need to declare variables.
Set wbSource = ActiveWorkbook
Set wsSource = wbSource.Sheets("BOM")   ' If do not want to define wbSource, can simply use Set wsSource = ActiveWorkbook.Sheets("BOM")

' You can open destination workbook (workbook 2) manually but I want to open during runtime
' This code will ask for destination Workbook
Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls; *.xlsx; *.xlsm; *.xlsb), *.xls; *.xlsx; *.xlsm; *.xlsb", Title:="Select a File")
If Fname = False Then Exit Sub                         'CANCEL is clicked

' Define opened Workbook as wbDest while opening it.
Set wbDest = Workbooks.Open(Filename:=Fname, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
' Define working sheet in wbB. Change sheet name accordingly
Set wsDest = wbB.Sheets("MPL")

' Now I can start copy date from wbSource to wbDest just by using variables. No need to call wbSource and wsSource anymore since rngSource include everything
Set rngSource = wsSource.Range("A2", "D31") ' you can also use Range("A2:D31")
rngSource.Copy wsDest.Range("B16")

' if do not want to define range. can use like this too
wsSource.Range("I2", "I31").Copy wsDest.Range("F16")

' You know you have data in wsDest from B16:B45 since you know yyou copy from wsSource A2:D31 (total 30 row)
' If you do not want to count, let code find last row
rowLast = wsDest.Range("A" & Rows.Count).End(xlUp).Row      ' This simulate selecting last row on column A. Then press Ctrl+ArrowUp. The cursor will stop on last occupied row in column A

' You can now defined range of data is wsDest
Set rngDest = wsDest.Range("A2", "A & rowlast")

' Instead of using For loop, another option is to use For each loop
n6 = 54
n8 = 47
For Each cell In rngDest
    ' Using Select Case instead of If statement probably mo flexible for multiple conditions
    Select Case Len(cell.Value)
        Case 6
            n6 = n6 + 1                                                 ' row increment
            wsDest.Range("A" & n7) = cell.Value
        Case 8
            n8 = n8 + 1                                                 ' row increment
            wsDest.Range("A" & n8) = cell.Value
        Case 10
        
    End Select
Next

End Sub