Whats wrong with this code?

[Phoenix_Turn]

Hi all,

This is my code so far, but it is not working:

Dim strE As String
Dim rngE As Range
Dim intE As Integer
Dim strA As String
Dim rngCell As Range, rngA As Range
Dim wksheet As Worksheet, i As Integer

'this becomes the active cell
Application.ActiveWorkbook.Worksheets("Sheet2").Activate
Application.ActiveWorkbook.Worksheets("Sheet2").Range("a1").Select

Set rngCell = Application.ActiveWorkbook.Worksheets("Sheet1").Range("a1")
Set wksheet = Application.ActiveWorkbook.Worksheets("Sheet1")

Do Until IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop

'For Each rngA In rngCell.Columns(1).Cells
If IsEmpty(ActiveCell) Then
ActiveCell.Value = rngCell
End If
'Next rngA

Now my main purpose is like this:

-Sheet 1, column A has a column of values
-Transfer those values to the first empty cell in row A in sheet 2, it below existing data in sheet2

-sheet 1 column B has a column of values
-Transfer those values to the first empty cell in row B in sheet 2, that is it is below the existing data in sheet2 column b

etc...

this can be done using a loop structure and an activecell, currentregion, resize format offsett function or anything that is simple.

Thanks guys! I appreciate your help

 

[kpark91]

thanks kpark, im looking for something like variables which consists of strings, integers, ranges and stuff. Not really pointing at "long" and etc.

Thanks for the reply though!

I have no idea what that means to use variables like strings, integers, ranges and stuff...
Would you like to elaborate more on that? In what ways is the code given different from what you're expecting?

Is the result wrong?

 

[Phoenix_Turn]

Not its not wrong, its just i dont understand the code being written because i never encountered those specifications before.

Im a n00b so in my first post, those sort of text makes sense to me.

 

[VoG]

Maybe take a look at Hiker's guide to learning VBA http://www.mrexcel.com/forum/showpost.php?p=2676997&postcount=5

 

[Phoenix_Turn]

Thanks for the heads up VoG!!!1

I'll try and read them in my lib.

Just this code, I need to solve out ASAP

 

[VoG]

You've been given several approaches, all of which work, so I don't see what the problem is.

 

[kpark91]

Well. then. I shall try to elaborate on my code with comments (starting with ')

'I have designated my name as LRA1, LRB1, ...
'because 'LR' stands for Last Row and the end 'A1' stands for column A of Sheet1
'Long is simply like an integer (is a datatype) but it is able to retain more numbers from -2 million to +2 million.
Dim LRA1 As Long, LRB1 As Long, LRA2 As Long, LRB2 As Long, count As Long
    
    'MAKE sure you're dealing with sheet1 and sheet2 in VBA, which can be found on the left pane of the VBA. It is not in the parenthesis
    LRA2 = Sheet2.Range("A" & Rows.Count).End(xlUp).Row 'Get row number of the last filled cell before the first blank row for column A of sheet2
    LRB2 = Sheet2.Range("B" & Rows.Count).End(xlUp).Row
    LRA1 = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
    LRB1 = Sheet1.Range("B" & Rows.Count).End(xlUp).Row
    
    count = 1 'This will be the variable that traverses through columnA of sheet1
    For i = LRA2 + 1 To LRA1 + LRA2 + 1 'Loop from LRA2 + 1 To LRA1 + LRA2 + 1 and i will be the variable that traverses through columnA of sheet2 but at the end so that we can append the data from sheet1
        Sheet2.Range("A" & i).Value = Sheet1.Range("A" & count).Value 'Copy from Sheet1 to Sheet2
        count = count + 1 'increment count so that it gets to the next filled in cell of column A in sheet1
    Next i 'increment i so that it gets to the next empty cell of column A in sheet2
    
    'same thing for columnB
    count = 1
    For i = LRB2 + 1 To LRA1 + LRB2 + 1
         Sheet2.Range("B" & i).Value = Sheet1.Range("B" & count).Value
        count = count + 1
    Next i

 

[Phoenix_Turn]

Well. then. I shall try to elaborate on my code with comments (starting with ')

'I have designated my name as LRA1, LRB1, ...
'because 'LR' stands for Last Row and the end 'A1' stands for column A of Sheet1
'Long is simply like an integer (is a datatype) but it is able to retain more numbers from -2 million to +2 million.
Dim LRA1 As Long, LRB1 As Long, LRA2 As Long, LRB2 As Long, count As Long
    
    'MAKE sure you're dealing with sheet1 and sheet2 in VBA, which can be found on the left pane of the VBA. It is not in the parenthesis
    LRA2 = Sheet2.Range("A" & Rows.Count).End(xlUp).Row 'Get row number of the last filled cell before the first blank row for column A of sheet2
    LRB2 = Sheet2.Range("B" & Rows.Count).End(xlUp).Row
    LRA1 = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
    LRB1 = Sheet1.Range("B" & Rows.Count).End(xlUp).Row
    
    count = 1 'This will be the variable that traverses through columnA of sheet1
    For i = LRA2 + 1 To LRA1 + LRA2 + 1 'Loop from LRA2 + 1 To LRA1 + LRA2 + 1 and i will be the variable that traverses through columnA of sheet2 but at the end so that we can append the data from sheet1
        Sheet2.Range("A" & i).Value = Sheet1.Range("A" & count).Value 'Copy from Sheet1 to Sheet2
        count = count + 1 'increment count so that it gets to the next filled in cell of column A in sheet1
    Next i 'increment i so that it gets to the next empty cell of column A in sheet2
    
    'same thing for columnB
    count = 1
    For i = LRB2 + 1 To LRA1 + LRB2 + 1
         Sheet2.Range("B" & i).Value = Sheet1.Range("B" & count).Value
        count = count + 1
    Next i

Appreciate your response!

But if it is not too much trouble can u write the code like the same format in my first post? thanks!

 

[Phoenix_Turn]

You've been given several approaches, all of which work, so I don't see what the problem is.

Yep! I know they are fine, but its not the same way i was taught. Like my first code, the style is different

 

[VoG]

Basically, you are asking us to teach you how to program badly. Sorry, but I'm not prepared to do that.

 

[Phoenix_Turn]

Basically, you are asking us to teach you how to program badly. Sorry, but I'm not prepared to do that.

Oh no!

I know how to program, but just the way i was taught is different to wat is encountered from your code and stuff. So in this situation with my task at hand, my manager wants to understand the code which is similiar to something from the author Mansfield: VBA for Microsoft Office 2007

But thanks for the help either way.