2 possible additions to your suggestions:
1) The code uses a "With.. With End" structure but nothing is relying on
that. Changing Cells() to .Cells() might actually improve things, especially
if this is running in
VB not Excel.
VB might be spending time resolving each
ambiguous Cells() occurance. Clarification might help performance.
2) The flip side of that: Value is the default property of Cells, so it
could probably be omitted.
HTH,
--
George Nicholson
Remove 'Junk' from return address.
"Bob Phillips" wrote in message
...
My biggest problem is that I couldn't get it to take a long time, 1/8th
second was all it took.
I could improve it with this
Set Ws = ActiveSheet: y =2
With Ws
Application.DisplayAlerts = False
For x = 501 To 2500
If (Cells(x, y).Value = 0) Then
Cells(x, y).Value = Cells((x - 1), y).Value
End If
Next
End With
Application.DisplayAlerts = True
but something else must be happening.
Do you have worksheet events? If so, turn them off beforehand, and
screenupdating and calculation
Application.EnableEvents = False
Application.Calculation = xlCalculation Manual
Application.ScreenUpdating = False
Set Ws = ActiveSheet
y = 4
With Ws
Application.DisplayAlerts = False
For x = 501 To 2500
If (Cells(x, y).Value = 0) Then
Cells(x, y).Value = Cells((x - 1), y).Value
End If
Next
End With
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculation Automatic
Application.EnableEvents = True
--
HTH
RP
(remove nothere from the email address if mailing direct)
"BillReese" wrote in message
news:2zKRe.48133$yv2.17668@trnddc04...
A zero reading from the spectrometer means that the refresh rate was not
good enough to record a value.. I taking maybe 6 to 20 columns and
dividing
it by the first 10 columns... The first ten columns many times have one,
two or 3 zeros in them at certain wavelengths... but there are times
when
all the columns are reading 0 at the same wavelength).. So if I get rid
of
the zeros then I'd still have an average of a number of columns divided
by
"nothing" (or empty cells).. so that will not solve my problem...
The truth is that there is no possible way to have a zero there, it's a
machine problem.. and I am stuck with the machine.. it has to do with
an
electrical impulse that does not dump it's voltage quick enough on a
refresh
and this is what causes the problem. The correct value for that point
is
extremely close to the previous value in the row before it.. I just need
to
refer each of those cells to the previous cell..
I can do what I need to do with the code I sent before.. But the code
is
horribly slow.. I think there must be a better way, but I don't know how
so
I am looking for an expert to point me in the right direction.
thanks,
BillReese
"Bob Phillips" wrote in message
...
Why not just change the formulae to ignore 0
IF(rng<0,rng)
within the formula, making it an array formula
--
HTH
RP
(remove nothere from the email address if mailing direct)
"BillReese" wrote in message
news:4NBRe.11389$um2.5658@trnddc03...
Hi,
I have a VB application, which starts and controls Excel. The program
reads
the data from over 20 ASCII files and extracts that data and stitches
it
together into one workbook by copy and paste actions.
Here is my problem, I do a lot of other things with my data, that goes
very
quickly... I mostly set ranges to do this and either move or modify
data
as ranges, or worksheet formulas. This code I past below is one
section
where I don't know how to work with it as a range.
My problem code iterates a column to and find any occurrence of the
value
"0".. If it finds a Zero it's a bad reading recorded by this
instrument
(a
spectrometer).. then I need to replace it with the previous row's
value
because an electrical dropout is not a real reading, and a ZERO give
me
divide by Zero errors for my other formulas added to the main
spreadsheet...
does anyone know of a faster way to do this code below. If I write
this
code inside an Excel file as a local macro, it runs extremely swift,
but
when I use Excel as a remote it takes maybe 20 to 50 seconds this
relatively
small iteration.
can someone please help me on this?
================================================== ==========================
=========
Dim Ws As Worksheet, x As Long, y As Long
Set Ws = ActiveSheet: y = 2
With Ws
Application.DisplayAlerts = False
For x = 1 To 2000
If (Cells((500 + x), y).Value = "0") Then
Cells((500 + x), y).Value = Cells((500 + (x - 1)),
y).Value
End If
Next
End With
Application.DisplayAlerts = True
================================================== ==========================
=========