Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    New Lounger
    Join Date
    Sep 2013
    Posts
    11
    Thanks
    4
    Thanked 0 Times in 0 Posts

    How to write VBA program

    hi every body


    i am a newbie in the vba programming
    please guide me how to write a vba program for the data taken from the sheet cols and paste accordingly in the res
    pleas look in the attachment file

    thanks in advance

    gvg
    Attached Files Attached Files

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Gold Lounger
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    3,463
    Thanks
    7
    Thanked 214 Times in 203 Posts
    Does this look like homework to anybody apart from me?

    I suggest you search the internet for "VBA copy column excel" for plenty of code examples.

    cheers, Paul

  4. #3
    New Lounger
    Join Date
    Sep 2013
    Posts
    11
    Thanks
    4
    Thanked 0 Times in 0 Posts
    hi paul

    thanks for your reply

    ill do it

    gvg

  5. #4
    New Lounger
    Join Date
    Sep 2013
    Posts
    11
    Thanks
    4
    Thanked 0 Times in 0 Posts
    hi paul

    as per your suggestion i googled VBA copy column excel but i didnot find any relevant topic to my issue

    please guide me how to do it

    thanks
    gvg

  6. #5
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,466
    Thanks
    3
    Thanked 130 Times in 123 Posts
    Paul's suggestions looked reasonable to me and the first few links in google are a good source of information.

    Perhaps you are asking an even more basic question of how do you create a macro in Excel. Perhaps searching google for "introduction vba excel" is another search you could do.

    Whilst we would like to help you with your problem, we don't want to avoid you actually learning something in your course of study. If we simply gave you the answer then you have learnt nothing. We would prefer that you actually make an effort to learn and then we can help fill in the gaps in YOUR knowledge.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  7. #6
    New Lounger
    Join Date
    Sep 2013
    Posts
    11
    Thanks
    4
    Thanked 0 Times in 0 Posts
    hi
    thank you for your reply

    i would like to know how to get a specific text from a sentence

    for example

    C O L U M N N O. 1 D E S I G N R E S U L T S
    LENGTH: 3000.0 mm CROSS SECTION: 600.0 mm X 600.0 mm COVER: 40.0 mm

    above two lines i want 1 in first line
    and 3000, 600 , 600 40 in the second line

    please guide me

    thanks

    gvg

  8. #7
    Gold Lounger
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    3,463
    Thanks
    7
    Thanked 214 Times in 203 Posts
    You can look for patterns and then use them to break the data into the required sections. In line 2 you have the colon ":" just before the information you want, so searching for the colon and then collecting the next few characters, removing leading and trailing spaces with a function like "trim", would work.

    cheers, Paul

  9. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,960
    Thanks
    193
    Thanked 730 Times in 666 Posts
    You could also consider the use of the Split function using a space as a delimiter. HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  10. #9
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,466
    Thanks
    3
    Thanked 130 Times in 123 Posts
    Using the split function as suggested by RetiredGeek you could create arrays to separate out the relevant information and transfer it to the other sheet like this

    Dim aArray() As String
    aArray = Split(Trim(Sheets("Cols").Range("A9").Text), " ")
    Sheets("Res").Range("F10").Value = aArray(2) & aArray(3)

    This is splitting the long string at each space and then transferring the relevant pieces into another cell
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  11. The Following User Says Thank You to Andrew Lockton For This Useful Post:

    gvgbabu (2013-09-15)

  12. #10
    New Lounger
    Join Date
    Sep 2013
    Posts
    11
    Thanks
    4
    Thanked 0 Times in 0 Posts
    hi andrew

    thank you for your reply

    i implemented as per your suggestion given above

    aArray = Split(Trim(Sheets("Cols").Range("A9").Text), " ")
    Sheets("Res").Range("F10").Value = aArray(2) & aArray(3)


    but i have encountered one problem is that

    i splited the text given below

    C O L U M N N O. 1 D E S I G N R E S U L T S

    i want "1" in the above text .
    Sheets("Res").Range("F10").Value = aArray(13) & aArray(14) & aArray(15)
    i got correct solution with the above



    if i have a text

    C O L U M N N O. 10 D E S I G N R E S U L T S

    for the same code i implemented

    Sheets("Res").Range("F10").Value = aArray(13) & aArray(14) & aArray(15)

    for this also i got the correct solution


    if i have a text

    C O L U M N N O. 100 D E S I G N R E S U L T S


    for the same code i implemented

    Sheets("Res").Range("F10").Value = aArray(13) & aArray(14) & aArray(15)

    for this also i am getting "100D" instead of "100"

    please guide me

    thanks
    gvg

  13. #11
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,960
    Thanks
    193
    Thanked 730 Times in 666 Posts
    How about trying this?
    Code:
    Option Explicit
    
    Sub ExtractDRNo()
    
       Dim vArray As Variant
       Dim lCntr  As Long
       Dim zTemp  As String
       
       For lCntr = 2 To 5
       
          zTemp = Replace(Cells(lCntr, 1), "  ", "*")
          zTemp = Replace(zTemp, " ", "")
          vArray = Split(zTemp, "*")
          Cells(lCntr, 2).Value = vArray(2)
       
       Next lCntr
    
    End Sub  'ExtractDRNo
    Extracted.JPG
    The logic here is to replace the doubled spaces with an asterisk {*} then eliminate all the single spaces, then use split with the asterisk as the delimiter.
    Of course you'll have to replace the Cells() with your cell/sheet references but the 4 lines of code in the loop are what I think you need. HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  14. The Following User Says Thank You to RetiredGeek For This Useful Post:

    gvgbabu (2013-09-15)

  15. #12
    New Lounger
    Join Date
    Sep 2013
    Posts
    11
    Thanks
    4
    Thanked 0 Times in 0 Posts
    hi RetiredGeek

    thank you for reply

    even using this code in loop also getting the same result as i said previously

    gvg

  16. #13
    New Lounger
    Join Date
    Sep 2013
    Posts
    11
    Thanks
    4
    Thanked 0 Times in 0 Posts
    hi RetiredGeek

    in my case spaces before the number is varying

    C O L U M N N O. 1 D E S I G N R E S U L T S---------- 5 spaces before 1
    C O L U M N N O. 10 D E S I G N R E S U L T S---------- 4 spaces before 10
    C O L U M N N O. 100 D E S I G N R E S U L T S---------- 3 spaces before 100

    gvg

  17. #14
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,960
    Thanks
    193
    Thanked 730 Times in 666 Posts
    Ok here's a fix:
    Code:
    Sub ExtractDRNo()
    
       Dim vArray  As Variant
       Dim lCntr   As Long
       Dim zTemp   As String
       Dim iSpaces As Integer
       
       For lCntr = 2 To 5
          For iSpaces = 9 To 2 Step -1
            zTemp = Replace(Cells(lCntr, 1), Space(iSpaces), "*")
          Next iSpaces
          'Don't know why I need the following line but always get 2 * before number?
          zTemp = Replace(zTemp, "**", "*")
          zTemp = Replace(zTemp, " ", "")
          vArray = Split(zTemp, "*")
          Cells(lCntr, 2) = vArray(2)
       
       Next lCntr
    
    End Sub  'ExtractDRNo
    Extracted.JPG
    Note: I used a monospaced font so the spaces would show in proper proportion.

    I wanted to use a Regular Expression to do the stripping but I'm not that familiar with them and couldn't get it to work. This is a little sloppy code wise but it does work as long as you don't get more than 9 spaces in a row.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  18. The Following User Says Thank You to RetiredGeek For This Useful Post:

    gvgbabu (2013-09-15)

  19. #15
    4 Star Lounger access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Exeter, UK
    Posts
    481
    Thanks
    50
    Thanked 36 Times in 33 Posts
    I've never used VBA in Excel, but have in Access (2003). Assuming that you have found the string "COLUMN NO.", try something like

    end = InStr(a, "DESIGN") -1
    start = 10
    answer = Val(Mid$(a, start, end))

    where a is your line, start is the first character after columnno. and end is the last character before design.
    Val converts the string to a number. You may have to experiment to determine the exact start and end of the number field. This should work for all numbers.

  20. The Following User Says Thank You to access-mdb For This Useful Post:

    gvgbabu (2013-09-15)

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •