Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create an array inside a For...Next loop
I will be looping through a dynamic range of cells, and when my condition is
met I want to add that value to an array, and then empty the contents of the array onto a different spreadsheet. As a simple example: 'Adding item to array For each cel in Range("A1:A100") If cell.value = "X" Then add cell value to my array end Next cell 'Writing data to new spreadsheet For each cell in Range("B1:B100") add array item to current cell Next cell Does anyone have any suggestions? Thanks in a advance! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create an array inside a For...Next loop
Sub test()
Dim r As Long Dim arr(1 To 100, 1 To 1) As String For r = 1 To 100 If Cells(r, 1) = "X" Then arr(r, 1) = "found" End If Next Workbooks.Open Filename:="C:\Test.xls" Sheets("Sheet2").Select Range(Cells(2), Cells(100, 2)) = arr End Sub RBS "Eric Winegarner" wrote in message ... I will be looping through a dynamic range of cells, and when my condition is met I want to add that value to an array, and then empty the contents of the array onto a different spreadsheet. As a simple example: 'Adding item to array For each cel in Range("A1:A100") If cell.value = "X" Then add cell value to my array end Next cell 'Writing data to new spreadsheet For each cell in Range("B1:B100") add array item to current cell Next cell Does anyone have any suggestions? Thanks in a advance! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create an array inside a For...Next loop
Hi Eric,
Tyr something like: '============== Public Sub DeleteRange() Dim Rng As Range Dim rCell As Range Dim copyRng As Range Dim WB As Workbook Dim SH As Worksheet Dim destSH As Worksheet Dim destRng As Range Dim CalcMode As Long Set WB = ActiveWorkbook '<<======== CHANGE Set SH = WB.Sheets("Sheet1") ' <<======= CHANGE Set Rng = SH.Range("A1:A100") '<<======= CHANGE Set destSH = WB.Sheets("Sheet2") '<<======= CHANGE Set destRng = destSH.Range("B2") '<<======= CHANGE With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With For Each rCell In Rng.Cells If rCell.Value = "X" Then If copyRng Is Nothing Then Set copyRng = rCell Else Set copyRng = Union(rCell, copyRng) End If End If Next rCell If Not copyRng Is Nothing Then copyRng.Copy Destination:=destRng Else 'nothing found, do nothing End If With Application .Calculation = CalcMode .ScreenUpdating = True End With End Sub '============== --- Regards, Norman "Eric Winegarner" wrote in message ... I will be looping through a dynamic range of cells, and when my condition is met I want to add that value to an array, and then empty the contents of the array onto a different spreadsheet. As a simple example: 'Adding item to array For each cel in Range("A1:A100") If cell.value = "X" Then add cell value to my array end Next cell 'Writing data to new spreadsheet For each cell in Range("B1:B100") add array item to current cell Next cell Does anyone have any suggestions? Thanks in a advance! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create an array inside a For...Next loop
Hi Eric,
Please ignore my response. I mis-read your question! --- Regards, Norman "Norman Jones" wrote in message ... Hi Eric, Tyr something like: '============== Public Sub DeleteRange() Dim Rng As Range Dim rCell As Range Dim copyRng As Range Dim WB As Workbook Dim SH As Worksheet Dim destSH As Worksheet Dim destRng As Range Dim CalcMode As Long Set WB = ActiveWorkbook '<<======== CHANGE Set SH = WB.Sheets("Sheet1") ' <<======= CHANGE Set Rng = SH.Range("A1:A100") '<<======= CHANGE Set destSH = WB.Sheets("Sheet2") '<<======= CHANGE Set destRng = destSH.Range("B2") '<<======= CHANGE With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With For Each rCell In Rng.Cells If rCell.Value = "X" Then If copyRng Is Nothing Then Set copyRng = rCell Else Set copyRng = Union(rCell, copyRng) End If End If Next rCell If Not copyRng Is Nothing Then copyRng.Copy Destination:=destRng Else 'nothing found, do nothing End If With Application .Calculation = CalcMode .ScreenUpdating = True End With End Sub '============== --- Regards, Norman "Eric Winegarner" wrote in message ... I will be looping through a dynamic range of cells, and when my condition is met I want to add that value to an array, and then empty the contents of the array onto a different spreadsheet. As a simple example: 'Adding item to array For each cel in Range("A1:A100") If cell.value = "X" Then add cell value to my array end Next cell 'Writing data to new spreadsheet For each cell in Range("B1:B100") add array item to current cell Next cell Does anyone have any suggestions? Thanks in a advance! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
use time functions inside if loop | Excel Worksheet Functions | |||
Using Loop to create Array | Excel Programming | |||
declaring active cell inside a for loop and offsetting from it | Excel Programming | |||
Check if a String is inside an Array | Excel Programming | |||
Check if a String is inside an Array | Excel Programming |