Hi I have got the following code from someone else - Sub MultiColsToA()
-
Dim rCell As Range
-
Dim lRows As Long
-
Dim lCols As Long
-
Dim lCol As Long
-
Dim ws As Worksheet
-
Dim wsNew As Worksheet
-
-
lCols = Columns.Count
-
lRows = Rows.Count
-
Set wsNew = Sheets.Add()
-
-
For Each ws In Worksheets
-
With ws
-
For Each rCell In .Range("B1", .Cells(1, lCols).End(xlToLeft))
-
.Range(rCell, .Cells(lRows, rCell.Column).End(xlUp)).Cut _
-
wsNew.Cells(lRows, 1).End(xlUp)(2, 1)
-
Next rCell
-
End With
-
Next ws
-
-
End Sub
-
This basically takes all columns from all worksheets in excel and pastes it into 1 column.
the problem is I don't understand the code, I want to know how I search google to learn what this code is doing
so I understand until line 11 but then I get lost
I am assuming Lines 13 and 20 say do the following code in each worksheet
then line 14 says in the current worksheet do the code begining in line 15
Line 15 Queries:
Query 1: "For Each rCell": Where is rCell given a value?
Query 2: For the rest of the code I assume its saying make the range from B1 to whatever the last column number is.
Line 16 Queries:
Query 1: I have no idea whats going on here
Line 17 Queries:
Query 1: I have no idea whats going on here
My objective is similar to this code but I want excel to take each consecutive set of 3 columns and paste it into the first 3 columns e.g.: take Column D,E and F and paste them underneath A,B and C. Then take Columns G, H and I also page them underneath A,B and C, and on it goes until all columns are pasted under Columns A, B and C.
Thank You
6 3515
if you debug it line by line (by pressing F8 instead of F5), each time you press F8 you'll find out what each line does.
about your questions:
lines 1 to 7 defines the variables
9 and 10 count the columns and rows
and then it make FORs for every worksheet, and every range in each worksheet.
The copy- paste code is in lines 16 and 17.
Well, what you have to do is quite simple, instead of making a FOR EACH, try using a FOR with STEP 3, and use it 3 times.
Oh, and check this out. It's my experience of how using variables is quite more efficient than using Copy-Paste in Excel's VBA. So why dont you give it a try this other way?
Ok, thanks I understand it a bit better but still not completely. Could you post the code that you think would work for what I want to acheive. Sorry I am feeling a bit thick in the brain at the moment.
Thank You
well, writing it for you wont solve your doubt, because you wont understand it... but please, at least check the FOR, DO, REDIM, RANGE and CELLS syntax, and check the article i recomended you before, so you can see why im writing it all into variants.
i came with something like this... - Sub ColsMerge()
-
Dim i As Integer, j As Integer, n As Integer
-
Dim x As Double, y As Double, z As Double
-
Dim a, b()
-
-
y = Rows.Count: x = 1
-
-
n = Int(InputBox("In how many columns will the sheet be merged?", "Merge Columns", "3"))
-
For i = 1 To Columns.Count
-
If Cells(1, i) = "" Then Exit For
-
z = Cells(1, i).End(-4121).Row
-
If z > x And z < y Then x = z
-
Next
-
a = Range(Cells(1, 1), Cells(x, i - 1))
-
ReDim b(1 To x * i / n, 1 To n)
-
For j = 1 To n
-
z = 1
-
For i = j To UBound(a, 2) Step n
-
y = 1
-
Do
-
If a(y, i) <> "" Then
-
b(z, j) = a(y, i)
-
z = z + 1
-
End If
-
y = y + 1
-
If z > Rows.Count Then Exit For
-
Loop Until y > x
-
Next
-
Next
-
Range(Cells(1, 1), Cells(UBound(b), n)) = b
-
End Sub
that is not quite error prove... and it has some limitations, like, it wont copy formulas, only values, and any empty colum will stop the columns searching. and it will skip empty cells... but that can be changed in the if in the middle of the DO/LOOP
HTH
Thanks kadghar Post # 4 helped me understand much better as I could actually see the code doing what I wanted
Good Post! Learning things by breaking up the task into smaller sub tasks makes it more clear to understand. There are tutorials available for coding languages but for VBA, a video tut is not available as open source. Any ideas of such videos folks, if yes then please do share the info. Thank You!
To be honest, I think you'll get better results by hitting Google to search for VBA tutorials.
You can also try asking in the Access/VBA forum, where our VBA experts hang out.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Bicho Verde |
last post by:
I have now free time and money to do what I want :-)
I have some basic skills in programming (C, Pascal, Macromedia
Actionscript) but don't know exactly what to do in the world of programming.
And...
|
by: Jacob |
last post by:
A problem I've been rather stuck on and, try as I might, have not been
able to find the answer to.
I've written a nice little class that I want to include in another
project. The class works...
|
by: Steve Jorgensen |
last post by:
I often come up with logic like this somewhere in a function:
....
If Not IsNull(<some expression>) Then
<default action>
Else
<alternative action>
End If
....
|
by: Ramzy Darwish |
last post by:
Hello,
I have a Bachelors in CS and a Masters in Comp. Graphics. In all of my
schoolwork, I used C and C++ and thought that I had a pretty good
understanding of the language(s). But now, as I...
|
by: johnb41 |
last post by:
I've been spending alot of time learning vb.net the past 6 months (for
regular winform apps. Been somewhat experienced in asp.net for a few
years)
But my style has been using Procedural...
|
by: aaronfude |
last post by:
Hi,
I'm an experienced C++ programmer (mostly in unix and other abstract
environments such as mex). I'm now getting into Windows programming and
finding out that there are many acronyms and...
|
by: Master Programmer |
last post by:
Thinking of learning VB.NET? New programmer? Thinking of Moving over
from VB 6.0?
Read on friend, let me help you make a more informed decision.........
Microsoft are a pathetic company,...
|
by: pandit |
last post by:
hai all,
i want to become a good programmer. one of my friends ( named
"arnuld", he posts here infrequently), taught me Lisp. so i am not a
programming beginner.
i have heard these 2 points....
|
by: Chris Carlen |
last post by:
Hi:
From what I've read of OOP, I don't get it. I have also found some
articles profoundly critical of OOP. I tend to relate to these articles.
However, those articles were no more objective...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |