Writing a loop in Excel for Visual Basic

Question

How do i write the following code as a loop. I want to copy values from a table in sheet 4 in a row from range (b:17:L17"). is there a more efficient way to do it with loops ?

ActiveSheet.Range("B17").Value = Sheets(4).Range("G8")

ActiveSheet.Range("C17").Value = Sheets(4).Range("G9")

ActiveSheet.Range("D17").Value = Sheets(4).Range("G10")

ActiveSheet.Range("E17").Value = Sheets(4).Range("G11")
ActiveSheet.Range("F17").Value = Sheets(4).Range("G12")
ActiveSheet.Range("G17").Value = Sheets(4).Range("G13")
ActiveSheet.Range("H17").Value = Sheets(4).Range("G14")

ActiveSheet.Range("I17").Value = Sheets(4).Range("G15")

ActiveSheet.Range("J17").Value = Sheets(4).Range("G16")


ActiveSheet.Range("K17").Value = Sheets(4).Range("G17")

ActiveSheet.Range("L17").Value = Sheets(4).Range("G18")

See flow control structures on Documentation.SO.

Mathieu Guindon
– Mathieu Guindon

2016-12-15 19:53:26 +00:00
Commented Dec 15, 2016 at 19:53 — Mathieu Guindon
– Mathieu Guindon, Commented Dec 15, 2016 at 19:53
How did this question get upvotes?

vacip
– vacip

2016-12-16 09:01:47 +00:00
Commented Dec 16, 2016 at 9:01 — vacip
– vacip, Commented Dec 16, 2016 at 9:01

A.S.H · Accepted Answer · 2016-12-15 19:45:20Z

8

Yes, there is:

ActiveSheet.Range("B17:L17").Value = Application.Transpose(Sheets(4).Range("G8:G18").Value)

answered Dec 15, 2016 at 19:45

A.S.H

29.4k6 gold badges25 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

A.S.H Over a year ago

@Gary'sStudent Thanks, but I just realized that the OP wanted a"more efficient way with loops", guess this probably is beyond expectation lol.

Sgdva Over a year ago

This is more efficient since Excel contains this function, definitely this is the best solution: neat and easy.

Community · Accepted Answer · 2017-05-23 12:24:33Z

1

You can, using something like this (VB.Net, but may copy easily to VBA):

Dim cell as Integer, c as Integer
cell = 8
For c = 66 To 76
   ActiveSheet.Range(Chr(c) & "17").Value = Sheets(4).Range("G" & cell)
   cell = cell + 1
Next

The Chr() function gets the character associated with the character code (66-76), and then this value is concatenated with the string "17" to form a complete cell name ("B17", "C17", ...)

I am also incrementing the cell number for G at the same time.

Use this if you really want to use a loop - but there could be better ways, like the answer given by @A.S.H

edited May 23, 2017 at 12:24

CommunityBot

11 silver badge

answered Dec 15, 2016 at 19:46

vbnet3d

1,1411 gold badge21 silver badges39 bronze badges

2 Comments

vbnet3d Over a year ago

@YowE3K Thanks - too aggressive on the highlighting.

vbnet3d Over a year ago

@Mat'sMug Absolutely right... this could be expanded to include a lot more possible values, but at that point I would recommend using something better any way, like A.S.H's answer.

Sgdva · Accepted Answer · 2016-12-15 22:00:14Z

Solution explanation:
Establish your rules! What is changing in the range for active sheet? The column is going to grow as the for/to cycle does! So, we should sum that to it. What is the another thing that is going to increment? The Range in the other side of the '=' so, by setting an algorithm, we may say that the row is const in the Activesheet range and the column is the on variable on the other side.
Solution:

Sub Test()
Const TotalInteractions As Long = 11
Dim CounterInteractions As Long
    For CounterInteractions = 1 To TotalInteractions
    'where 1 is column A so when it starts the cycle would be B,C and so on
    'where 7 is the row to start so when it begins it would became 8,9 and so on for column G
    ActiveSheet.Cells(17, 1 + CounterInteractions).Value = Sheets(4).Cells(7 + CounterInteractions, 7)
    Next CounterInteractions
End Sub

help-info.de · Accepted Answer · 2017-01-14 16:44:38Z

0

This is probably your most efficient solution in a with statement:

Sub LoopExample()
  Sheets("Sheet4").Range("G8:G18").Copy
  Sheets("Sheet2").Range("B17").PasteSpecial xlPasteValues, Transpose:=True
End Sub

edited Jan 14, 2017 at 16:44

help-info.de

7,38817 gold badges43 silver badges43 bronze badges

answered Dec 15, 2016 at 20:00

mojo3340

5691 gold badge6 silver badges27 bronze badges

4 Comments

YowE3K Over a year ago

That will copy more than just .Values. It also copies to the wrong sheet (should be Sheets(4)) and it also copies to cells G8:Q8 rather than to G8:G18. And the source and destination are the wrong way around.

A.S.H Over a year ago

@YowE3K You are impressive! :D

YowE3K Over a year ago

It's not going to work without a transpose at a minimum, so probably you want to do a copy followed a pastespecial values transpose.

YowE3K Over a year ago

(a) Sheets("Sheet2") should be ActiveSheet. (I assume "Sheet2" was where you were testing.) (b) There is no point in having a With block if you are only using each object once. (c) The downside of using the clipboard to do a Copy/Paste is that some other application (or the user) may use the clipboard between when the Copy occurs and the Paste occurs, thus corrupting your data. That's one of the reasons why I believe A.S.H's solution is better - it bypasses the clipboard.

Collectives™ on Stack Overflow

Writing a loop in Excel for Visual Basic

4 Answers 4

2 Comments

2 Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

2 Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related