C RUBY-ON-RAILS MYSQL ASP.NET DEVELOPMENT RUBY .NET LINUX SQL-SERVER REGEX WINDOWS ALGORITHM ECLIPSE VISUAL-STUDIO STRING SVN PERFORMANCE APACHE-FLEX UNIT-TESTING SECURITY LINQ UNIX MATH EMAIL OOP LANGUAGE-AGNOSTIC VB6 MSBUILD

# VBA Excel 2007 : Need to loop copy and loop count number except zero every row above

By : Stranger Guy
Date : November 18 2020, 01:01 AM
help you fix your problem i'm a complete noob in vba so i'm searching all over the net to combine the code but right now it seems i hit the great wall and can't get it right. what i wanna do are:
code :
``````Sub Copy_Sum()

Dim ws As Worksheet
'Selecting the worksheets to loop through
K = 1
For Each ws In ThisWorkbook.Worksheets
'Skiping the sheet1
If ws.Name <> "Sheet1" Then
'Counting the number of rows for automation
rowsCount = Cells(Rows.Count, 1).End(xlUp).Row
temp = 0
'Looping throught the cells for the calculation
For j = 2 To (rowsCount)
'Counting the number of cells which value greater than zero
If Cells(j - 1, 1) > 0 Then
temp = temp + 1
End If
Next j
'Summing up the values which are above the current cell and in Sheet1, this inclues negative numbers as well

Cells(rowsCount + 1, 1).Value = Application.Sum(Range(Cells(1, 1), Cells(rowsCount, 1)))
Cells(rowsCount + 1, 2) = temp
Worksheets("Sheet1").Cells(K, 1).Value = Cells(rowsCount + 1, 1).Value
Worksheets("Sheet1").Cells(K, 2).Value = temp
K = K + 1
End If

Next ws

End Sub
``````

Share :

## Optimizing loop for vba macro excel 2007

By : user3514529
Date : March 29 2020, 07:55 AM
I hope this helps . There is not so much code that seems to be obviously inefficient.
Here is some tips about what I can tell:
code :
``````Sub test()
Dim rng As Range, row As Range
Dim i As Long

Dim textval As String
Dim limitz As String
Dim remaining As String

Set rng = Range("G2", Range("B1").End(xlDown).Offset(0, 5))
i = 1

For Each row In rng.Rows
With row
textval = .Cells(i).Value
limitz = Left(textval, 1)

If limitz = "" Then
.Cells(i).EntireRow.Delete
ElseIf limitz <> "-" And Not IsNumeric(limitz) Then
remaining = Right(textval, Len(textval) - 1)
With .Cells(i)
.Value = remaining
.Offset(0, 1).Value = limitz
End With
i = i + 1
Else
i = i + 1
End If
End With
Next
End Sub
``````

## Loop and count excel cells containing data, then loop over an action that many times

By : ccc lx
Date : March 29 2020, 07:55 AM
it should still fix some issue Something like this which
avoids looping for the cell count, using Application.CountA instead uses FileCopy to copy the files incrementally
code :
``````Sub CopyEm()
Dim ws As Worksheet
Dim strIn As String
Dim strOut As String
Dim strFile As String
Dim strLPart As String
Dim strRPart As String
Dim lngCnt As Long
Dim lngFiles As Long
Set ws = Sheets("Test")
lngCnt = Application.CountA(ws.Columns("A"))
If lngCnt = 0 Then Exit Sub
strIn = "C:\image\"
strOut = "C:\imagecopy\"
strFile = "test.tif"
'extract string portions of the file name and type outside the copy loop
strLPart = Left\$(strFile, InStr(strFile, ".") - 1)
strRPart = Right\$(strFile, Len(strFile) - Len(strLPart))
For lngFiles = 1 To lngCnt
FileCopy strIn & strFile, strOut & strLPart & "(" & lngFiles & ")" & strRPart
Next
End Sub
``````

## Excel VBA Copy and Paste Loop within Loop

By : Maxim Nasurdinov
Date : March 29 2020, 07:55 AM
This might help you There are many ways to do this, and some are more efficient than others. My solution may not be the most efficient, but hopefully it will be easy for you to understand so that you can learn.
It's very difficult to understand what you're attempting to do in activity three, so I wasn't able to provide a solution to that step. Use my code as a template for step three and if you run into issues, feel free to leave a comment.
code :
``````Sub testLoopPaste()

Dim i As Long
Dim ii As Long
Dim i3 as Long
Dim LastRow As Long
Dim wb As Workbook
Dim sht1 As Worksheet
Dim sht2 As Worksheet

Set wb = ThisWorkbook
Set sht1 = wb.Sheets("Sheet1")
Set sht2 = wb.Sheets("Sheet2")

'Find the last row (in column A) with data.
LastRow = sht1.Range("A:A").Find("*", searchdirection:=xlPrevious).Row
ii = 2

'This is the beginning of the loop
For i = 3 To LastRow
'First activity
sht2.Range("A" & ii) = sht1.Range("F" & i).Value
sht2.Range("B" & ii) = sht1.Range("D" & i).Value
sht2.Range("C" & ii) = sht1.Range("A" & i).Value
sht2.Range("D" & ii) = sht1.Range("H" & i).Value
ii = ii + 1

'Second activity
sht2.Range("A" & ii) = sht1.Range("F" & i).Value
sht2.Range("B" & ii) = sht1.Range("D" & i).Value
sht2.Range("C" & ii) = sht1.Range("A" & i).Value
sht2.Range("D" & ii) = sht1.Range("I" & i).Value
ii = ii + 1

'Third activity
For i3 = 1 To sht1.Range("K" & I)
sht2.Range("A" & ii) = sht1.Range("F" & i).Value
sht2.Range("B" & ii) = sht1.Range("D" & i).Value
sht2.Range("C" & ii) = sht1.Range("A" & i).Value
ii = ii + 1
Next i3

Next i

End Sub
``````

## VBA Loop Through Pivot Table Excel 2007

By : English to Spanish T
Date : March 29 2020, 07:55 AM
wish of those help Here are some tips for rewriting this code to be a bit easier to follow and doesn't use common pitfalls of Active and goto statements.
code :
``````Sub m4_HCAHPS_Macro()

Dim vPhys2 As String
Dim vrow2 As Long: vrow2 = 1
Dim vlastphys2 As String

Dim wksDoctors As Worksheet
Dim wkshcahps As Worksheet

Set wkshcahps = Sheets("hcahps")
Set wksDoctors = Sheets("hcahps doctors")

vPhys2 = wksDoctors.Range("A" & CStr(vrow2)).Value

Do While (Len(vPhys2) < 1)
wkshcahps.PivotTables("HcahpsPivotcurrentTable").PivotFields("Doctor").CurrentPage = vPhys2
wkshcahps.PivotTables("HcahpsPivotTrendTable").PivotFields("Doctor").CurrentPage = vPhys2

vrow2 = vrow2 + 1
vlastphys2 = vPhys2

vPhys2 = wksDoctors.Range("A" & CStr(vrow2)).Value
Loop

MsgBox "All Done Here"
End Sub
``````
``````With ActiveSheet.PivotTables("HcahpsPivotTrendTable").PivotFields("Doctor")
.CurrentPage = vPhys2
End With
``````

## Excel vba copy and paste loop within loop - limit range

By : Rodrigo Rosmaninho
Date : March 29 2020, 07:55 AM
help you fix your problem You were looking for the last row but only looking within the populated area. I would suggest changing the method that the last row is determined by starting at the bottom of the worksheet and finding the last populated cell in column C. This would be like being in C1048576 and tapping Ctrl+▲.