Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Creating Arrays / Do Loops

Posted on 2004-11-22
3
Medium Priority
?
151 Views
Last Modified: 2010-05-02
This is a program that should move the old fat to next column and remove old fat there.  Then it needs to scan across a series of columns in row 2 until it finds the value true.  Next it will drop to row 11 and copy down to row 22 and paste into new column where old fat moved from.  What i need is to do this in sequence.  As I am new to this program I am unsure on what to do.

 
Sub OldFat()
' This is used to move the old fat values to another column
Range("e11:e22").Select
Selection.Copy
Range("f11:f22").Select
ActiveSheet.Paste
Range("e11:e22").Select
Selection.ClearContents
End Sub

Sub ClearFat()
'this is used to clear all the the old fat values from the
'table to put in new ones
Range("f11:f22").Select
Selection.ClearContents
End Sub

I have managed to get this work for a particular column so that it will select the values and paste them over.  What I then want it to do is obviously replace the E value with a letter so that it can select the relevant column.  In addition to this I also need it to scan for the right column before it does the code below  For this i believe that i need to use an array or a loop.  I am not sure how to do this though.

Sub TEST()
Range("E11").Select
ActiveCell.FormulaR1C1 = "=OFFSET(R[-9]C[3],9,0,1,1)"
Range("E12").Select
ActiveCell.FormulaR1C1 = "=OFFSET(R[-9]C[3],9,0,1,1)"
Range("E13").Select
ActiveCell.FormulaR1C1 = "=OFFSET(R[-9]C[3],9,0,1,1)"
Range("E14").Select
ActiveCell.FormulaR1C1 = "=OFFSET(R[-9]C[3],9,0,1,1)"
Range("E15").Select
ActiveCell.FormulaR1C1 = "=OFFSET(R[-9]C[3],9,0,1,1)"
Range("E16").Select
ActiveCell.FormulaR1C1 = "=OFFSET(R[-9]C[3],9,0,1,1)"
Range("E17").Select
ActiveCell.FormulaR1C1 = "=OFFSET(R[-9]C[3],9,0,1,1)"
Range("E18").Select
ActiveCell.FormulaR1C1 = "=OFFSET(R[-9]C[3],9,0,1,1)"
Range("E19").Select
ActiveCell.FormulaR1C1 = "=OFFSET(R[-9]C[3],9,0,1,1)"
Range("E20").Select
ActiveCell.FormulaR1C1 = "=OFFSET(R[-9]C[3],9,0,1,1)"
Range("E21").Select
ActiveCell.FormulaR1C1 = "=OFFSET(R[-9]C[3],9,0,1,1)"
Range("E22").Select
ActiveCell.FormulaR1C1 = "=OFFSET(R[-9]C[3],9,0,1,1)"
End Sub
0
Comment
Question by:LozLaura17
2 Comments
 
LVL 26

Accepted Solution

by:
EDDYKT earned 800 total points
ID: 12643877
Sub TEST()

dim i
Range("E11").Select

for i=1 to 12
ActiveCell.FormulaR1C1 = "=OFFSET(R[-9]C[3],9,0,1,1)"
ActiveCell.Offset(1, 0).Select
next
End Sub
0
 
LVL 55

Expert Comment

by:Jaime Olivares
ID: 12644201
Sub TEST(column as String)
dim row as Integer
dim cell as String

For row=11 to 22
     cell = column & str(row)
     ActiveCell.Range(cell).Select
     ActiveCell.FormulaR1C1 = "=OFFSET(R[-9]C[3],9,0,1,1)"
Next

End Sub
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question