Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

VBS Loop Help


  • Please log in to reply

#1
biscuitmunchy

biscuitmunchy

    New Member

  • Member
  • Pip
  • 1 posts
Hi All I need help, i'm new in writing VBS script..
i make one simple script just to copy, paste and delete..
but the problem is, how to make the script do the same thing repeated, with just one cell change..

Please help me, already google and do some experiment but still with dead end. :)


Const xlSaveChanges = 1
Dim objXLApp, objXLWb,

Set objXLApp = CreateObject("Excel.Application")
objXLApp.Visible = True

Set objXLWb = objXLApp.Workbooks.Open("C:\Documents and Settings\Username\Desktop\.xls")
Set My = objXLWb.Worksheets("sheet1")


,Copy and paste (From here)
'-------------------------------------------------------

My.Activate
My.Range("F5:I5").select
objXLApp.selection.copy
My.Range("M2").select
My.paste

My.Range("F7:I7").select
objXLApp.selection.copy
My.Range("M3").select
My.paste

MySheet.Range("F18:I18").select
objXLApp.selection.copy
MySheet.Range("M4").select
Mysheet.paste

My.Range("F19:I19").select
objXLApp.selection.copy
My.Range("M5").select
My.paste

My.Range("F55:I55").select
objXLApp.selection.copy
My.Range("M6").select
My.paste


'-------------------------------------------------------
'Change only value on cell A5 to A6,A7,A8....
'-------------------------------------------------------

objXLWb.Worksheets("Sheet1").Range("M2:P10").Copy
objXLWb.Worksheets("Sheet2").Activate
objXLWb.Worksheets("Sheet2").Range("A5").PasteSpecial -4163,-4142,False,True

Delete old data (to here)
'-------------------------------------------------------
mysheet.Activate
MySheet.Range("1:198").delete


Need help from you guys...
thanks :)
  • 0

Advertisements


#2
AceInfinity

AceInfinity

    Visiting Staff

  • Visiting Consultant
  • 34 posts
  • MVP

but the problem is, how to make the script do the same thing repeated, with just one cell change..


Loop through the cell numbers/letters that you want to change concatenating them together when you reference the ranges..
  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP