For Each Copies Only Last Row 2

[JoeMicro]

Hi Everyone

i m trying to do a For Each Statement,
for Ex: for each row it should paste some cells to another sheet, but it does the command only for the last row.

This is the Code:

Sub EnterIncome()
    
    Dim rcrdRow As Long
    Dim pstRow As Long
    Dim shAccount As Worksheet
    Dim shRecord As Worksheet
    Dim accRow As Range
    Dim cName As String, cDT
    Dim cAccount, cAmount As Range
    
    Set shRecord = Sheet2
        pstRow = shRecord.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
     
        
        cName = Sheet1.Range("C5")
        cDT = Sheet1.Range("E7")
      
       For Each accRow In Range("C10:C14")
            If Len(accRow) > 0 Then
                shRecord.Cells(pstRow, 1).Value = cDT
                shRecord.Cells(pstRow, 2).Value = cName
                shRecord.Cells(pstRow, 5).Value = accRow.Offset(0, 2)
                shRecord.Cells(pstRow, 3).Value = accRow.Offset(0, 1)
                shRecord.Cells(pstRow, 4).Value = accRow
            End If
        Next accRow
End Sub

Thanks

 

[danomaniac]

Your code works fine, it just overwrites the previous data each time it goes through the IF..Then statement making it look as though it only works on the last one... Just add a line incrementing your variable 'pstRow' by one just before the 'End If'...

"For Each accRow In Range("C10:C14")
If Len(accRow) > 0 Then
shRecord.Cells(pstRow, 1).Value = cDT
shRecord.Cells(pstRow, 2).Value = cName
shRecord.Cells(pstRow, 5).Value = accRow.Offset(0, 2)
shRecord.Cells(pstRow, 3).Value = accRow.Offset(0, 1)
shRecord.Cells(pstRow, 4).Value = accRow
pstRow = pstRow + 1
End If
Next accRow
"

"It's more like it is now, than it ever has been."

 

[SkipVought]

Joe,

First. good for you, for quallifying each range to it's parent sheet object.

Just another item to consider.

A single cell range, like Sheet1.Range("C5") has MANY properties, one of which is the Value property. Happens that Value is the default property, so Sheet1.Range("C5") returns the VALUE that is in Sheet1!C5. Point is, it would ALSO be a good habit to fully specify which property of the referenced object, you are referencing...

        cName = Sheet1.Range("C5").Value
        cDT = Sheet1.Range("E7").Value

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[JoeMicro]

Thanks danomaniac How a simple thing could be so frustrating.

Thanks Skip for your input its all much helpful since i m self trained.

Thanks again

Joe

 

[WalkerEvans]

JoeMicro,

First, a very large percentage of the folks that post here are self-trained; many of us had no choice as formal training was/is not available.

Second, if you want to thank someone for their help, use the little magenta "star-thingie" (ie, *) at the bottom of their post. This serves the dual purpose of letting others know that the post contains valuable information, as well as giving the poster a shot at TipMaster of the week. No real monetary value to that last, just "braggin' rights".

Finally, if you check back here from time to time, even when you don't have a problem, you may end up being the one to solve someone else's head-bamger. This is what this site is built on ... people helping each other. Welcome!



----------------------------------------------------------------------------------
"A committee is a life form with six or more legs and no brain." -- L. Long

 

[danomaniac]

..Formal Training?" What's that?

"It's more like it is now, than it ever has been."

 

[WalkerEvans]

danomaniac,

It really does exist ... sorta. My bosses here have sent me to four (count 'em, FOUR!) Excel classes which are generally acknoledged to be the best offered between the Appalachians and the Rockies, and I did learn a few things. Nonetheless, when it came to coding (macros) there were two standard answers: 1) We don't cover that, or; 2) I do know a lot about that, but I don't teach it and my boss won't let me share data not in the course outline.

However, I did hear from a reliable source (my aunt's wash-woman's sister's son, who got it from a policeman, who heard it from some friends in Alaska, who claim to have gotten it directly from a high-society lady, who claims to personally know Bill Gates) that Microsoft is going to be launching detailed seminars on VBA in the immediate future!

[small](Of course, I did hear that in 1998.)[/small]



----------------------------------------------------------------------------------
"A committee is a life form with six or more legs and no brain." -- L. Long

 

[SkipVought]

I, two, am self trained. I did take an adult ed course in Dartmouth BASIC (Beginners All-purpose Symbolic Instruction Code) circa. 1972, on a Digital PDP-8 teletype I/O.

1. When I got Excel & Word in 1989, I discovered VBA, the macro recorder et al. WOW!!! Taught myself to do a few things in VBA.

2. In 1994, I decided to replicate a graphic project that I had designed in COBOL, in Excel. I wanted to add some controls, but did not have a clue. Fortunately, a guy at our conpany's help desk, had quite a bit of hands on knowledge of VBA, and I picked his brain.

3. Then I discovered Tek-Tips in 2001. WOW!!!

Those have been my three major steps in learning VBA.

Number 3 is yet IN WORK!

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[WalkerEvans]

Skip,

Sounds a lot like the story a lot of us could tell with only minimal modification.

1. I discovered WANG Glossaries in 1981 and through trial and error (a LOT of error!) taught myself how to program in that pseudo-language environment.

2. In 1992 everyone in our offices got PC terminals with Office included and I discovered VBA and the recorder . A new world opened and after a time it somehow developed that: 1) I had a talent for coding, or; 2) everyone else around here wasn't interested in learning. No matter which cause (the 2nd is likely) I became the VBA guru for the building.

3. About this time last year I discovered Tek-Tips. Trumpets and Flourishes! Cymbals crashing! Lightning flashes illuminating the dark areas of my mind!

Okay, maybe #3 is a bit exaggerated, but that's how it felt - a place where I could ask my stupid questions and get actual, helpful information from folks who didn't seedm to think my questions were all that stupid. Questions still come up, the answers to some turning out to be so simple that I get dents in my forehead from banging it on the desk ; on the other hand, I know that once or twice my input has gotten others started down the right path, which is more rewarding than I would ever have believed.

I expect to be in Learning Point #3 for quite some time. If nothing else, I like the company!

----------------------------------------------------------------------------------
"A committee is a life form with six or more legs and no brain." -- L. Long

 

[SkipVought]

#3 Ditto & Amen!!!!!

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]