CODEWITHSUNDEEP
vbaexcel
Siddhanth Khanvilkar
Siddhanth Khanvilkar

Reputation: 29

Writing a loop in Excel for Visual Basic

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")

Upvotes: 2

Views: 467

Answers (4)

mojo3340
mojo3340

Reputation: 549

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

Upvotes: 0

Sgdva
Sgdva

Reputation: 2800

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

Upvotes: 1

vbnet3d
vbnet3d

Reputation: 1151

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

Upvotes: 1

A.S.H
A.S.H

Reputation: 29332

Yes, there is:

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

Upvotes: 8

Related Questions