Jump to content

Welcome to Geeks to Go - Register now for FREE

Need help with your computer or device? Want to learn new tech skills? You're in the right place!
Geeks to Go is a friendly community of tech experts who can solve any problem you have. Just create a free account and post your question. Our volunteers will reply quickly and guide you through the steps. Don't let tech troubles stop you. Join Geeks to Go now and get the support you need!

How it Works Create Account
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