Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
| Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old August 9th, 2005, 09:02 PM
ct ct is offline
Authorized User
 
Join Date: Aug 2005
Location: as, kedah, Malaysia.
Posts: 28
Thanks: 0
Thanked 0 Times in 0 Posts
Default coding for "update" button..

actually..i have a problem to write a coding for update button in VB..can u help me convert this formula from lotus.123 to VB in excel..

this is the formula for "UPDATE" button lotus.123:

Sub Click(Source As Buttoncontrol)
    [31W Casing].MoveOrigin $Down,39
    [Prod:C47..Prod:C53].Select
    Selection.CopyToClipboard
    [Prod:E34].Select
    Selection.Paste ,False,PasteData + PasteFormulas,,,,
    [Window 2].Activate
    [Prod:D47..Prod:D53].Select
    Selection.CopyToClipboard
    [Prod:E47].Select
    Selection.Paste ,False,PasteData + PasteFormulas,,,,
    [Window 2].Activate
    [Prod:G49..Prod:i49].Select
    Selection.CopyToClipboard
    [Prod:G29].Select
    Selection.Paste ,False,PasteData + PasteFormulas,,,,
    [Window 2].Activate
    [Prod:j49..Prod:j49].Select
    Selection.CopyToClipboard
    [Prod:w29].Select
    Selection.Paste ,False,PasteData + PasteFormulas,,,,
    [Window 2].Activate
    [Prod:I45].Select
    Selection.CopyToClipboard
    [Prod:I43].Select
    Selection.Paste ,False,PasteData + PasteFormulas,,,,
    [Window 2].Activate
    [Prod:R47..Prod:S55].Select
    Selection.CopyToClipboard
    [Prod:T47].Select
    Selection.Paste ,False,PasteData + PasteFormulas,,,,
End Sub

can u help me convert this coding to VB because i want to create same button but in VB..can u help me plizz
tq

  #2 (permalink)  
Old August 10th, 2005, 02:23 AM
Friend of Wrox
 
Join Date: Jan 2005
Location: Bournemouth, Dorset, United Kingdom.
Posts: 180
Thanks: 0
Thanked 0 Times in 0 Posts
Default

this should be sending you in the right sort of direction...

    ActiveCell.Offset(39, 0).Select
    Sheets("prod").Range("C47:C53").Copy
    Sheets("prod").Range("E34").Select
    Selection.PasteSpecial
    Sheets("prod").Range("D47:D53").Copy
    Sheets("prod").Range("E47").Select
    Selection.PasteSpecial
    Sheets("prod").Range("G49:i49").Copy
    Sheets("prod").Range("G29").Select
    Selection.PasteSpecial
    Sheets("prod").Range("j49:j49").Copy
    Sheets("prod").Range("w29").Select
    Selection.PasteSpecial
    Sheets("prod").Range("I45").Copy
    Sheets("prod").Range("I43").Select
    Selection.PasteSpecial
    Sheets("prod").Range("R47:S55").Copy
    Sheets("prod").Range("T47").Select
    Selection.PasteSpecial

As a point, if you are using excel and are not experienced but willing to learn the ways of vba, it would be particularly useful for you to use the macro...record new macro functionality and then view the code that is recorded as you process the actions. The code tends to be padded out and excessive but it shows you exactly what steps you processed.

cheers

Matt

  #3 (permalink)  
Old August 11th, 2005, 09:42 PM
ct ct is offline
Authorized User
 
Join Date: Aug 2005
Location: as, kedah, Malaysia.
Posts: 28
Thanks: 0
Thanked 0 Times in 0 Posts
Default

i have same problem to convert the coding from lotus to excel..but it was different coding from the previous...can u help me to convert the coding...
i really need ur help...

Sub Click(Source As Buttoncontrol)
    [PCR].MoveCellPointer $Home,1
    [PCR].MoveOrigin $PgRight,1
    [PCR].MoveOrigin $PgRight,1
    [PCR].MoveOrigin $Left,1
    [PCR].MoveOrigin $Left,1
    [PCR].MoveOrigin $Left,1
    [PCR].MoveOrigin $Left,1
    [PCR:AD7..PCR:AG728].Select
    Selection.CopyToClipboard
    [PCR].MoveCellPointer $Home,1
    [PCR:N7].Select
    Selection.Paste ,False,PasteData + PasteFormulas,,,,
    [Window 2].Activate
    [PCR].MoveOrigin $Right,3
    [PCR].MoveOrigin $Right,20
    [PCR:AI7..PCR:AL728].Select
    Selection.CopyToClipboard
    [PCR].MoveOrigin $PgUp,1
    [PCR].MoveOrigin $PgLeft,1
    [PCR].MoveOrigin $PgUp,1
    [PCR:T7].Select
    Selection.Paste ,False,PasteData + PasteFormulas,,,,
    [Window 2].Activate
    [PCR].MoveCellPointer $Home,1
End Sub


besides that...can u explain what it suppose to mean.."+U47" is the formula in lotus.123 and i have to convert it in excel...how i want to convert it because i don't understand the formula..
i really appreciate if u can help me..
tq

  #4 (permalink)  
Old August 12th, 2005, 02:31 AM
ct ct is offline
Authorized User
 
Join Date: Aug 2005
Location: as, kedah, Malaysia.
Posts: 28
Thanks: 0
Thanked 0 Times in 0 Posts
Default

can u help me solve the problem...i hve already post the msg in forum...i really appreciate if u can help me solve the problem..tq

  #5 (permalink)  
Old August 12th, 2005, 02:55 AM
Friend of Wrox
 
Join Date: Jan 2005
Location: Bournemouth, Dorset, United Kingdom.
Posts: 180
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi ct

here you go, I think it does all you need it to.

    Sheets("PCR").Select
    Range("AD7:AG728").Select
    Selection.Copy
    Sheets("PCR").Range("N7").Select
    Selection.PasteSpecial
    'Workbooks(2).Activate
    Sheets("PCR").Range("AI7:AL728").Select
    Selection.Copy
    Sheets("PCR").Range("T7").Select
    Selection.PasteSpecial
    'Workbooks(2).Activate


cheers

Matt



Similar Threads
Thread Thread Starter Forum Replies Last Post
Update Button in DataGrid Bibin .NET Framework 1.x 1 January 28th, 2006 08:22 AM
HELP!!!Coding a command button to open a form solva Beginning VB 6 5 May 10th, 2005 09:56 PM
Confirm Button after update fixitman Access 2 April 12th, 2004 03:28 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.