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 3521 kadghar 1,295
Recognized Expert Top Contributor
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
kadghar 1,295
Recognized Expert Top Contributor
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 also I don't know exactly why would I learn Python rather than C#,
C++ or Perl. Basicaly I don't know where to start, if there is much to do or
if...
|
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 fine if I have it all in one big file, but I run
into problems when I try to break it down into source and header files. I
get a linker error when I...
|
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 really am trying to find
work as a C/C++ programmer, I am having trouble on programming interview
tests when they start asking about low-level C stuff...
|
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 techniques, rather than OOP.
Lately i've read more and more how OOP is the way to go. So i'm trying
to learn it, and boy is it difficult if you've not...
| |
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 technologies. So I'm
trying to figure out what to start learning.
It seems that .Net will replace MFC so might as well not invest time in
MFC.
|
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, but they have got worse over the last
5 years. Here are just a few reasons (there are many more), not to
sacrifice
|
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. i want to know how :
1. C gives you a strong base of Procedural style of programming which
forms the basis of learning other paradigms e.g OOP
|
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 than the descriptions of
OOP I've read in making a case. Ie., what objective
data/studies/research indicates that a particular problem can be...
|
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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
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, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
| |
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 tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
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 Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
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, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
| |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |