How do I find one or more zeros in an Array ?

nco

New Member
Joined
Feb 9, 2005
Messages
23
Hi super Excel users

I have one more questions for you today.
Hope you can help me out on this one.

The essence of my problem is:
I have an Array which contain 200 to 300 numbers, (in one dimension only)

Then this Array can contain one or more zeros and I want to get the index.numbers of those zeros.
If the Array do not contain any exact zeros it might contain one or more numbers that are pretty close to zero and have zero slope (saddle points). If the Array doesn't contain any exact zeros I want to get the index numbers of those points being close to zero and at the same time have zero slope.

Is it possible to do this in pure VBA in an easy way without writing all the numbers in the Array to a range in Excel ?


Hope one of you nerds out there can help me out on this one.

Regards
NCO
Denmark

All for today :confused:
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
What is your definition of pretty close to zero and what is zero slope?
 

nco

New Member
Joined
Feb 9, 2005
Messages
23
Finding zeros in an Array

Hi Hotpepper

I have been thinking more about the problem.
And has come to the conclusion that I should forget about the values close to zero, and if there aren't any zero solutions then pressent a pop up window to the user explaning that he or she has to enter new numbers in order to get exact solutions.

So the problem is now reduced to finding 1 or 2 zeros in an Excel VBA Array.
Preferrably purly in VBA - coding and not writing the complete Array to a sheet.


Hope you have some inputs.
Regards NCO
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
How is the array being filled with values?
 

nco

New Member
Joined
Feb 9, 2005
Messages
23
Zeros in an Array.....

Hi Hotpepper

The code that assigns values to my Array's is listed below.
What I actually are interested in is the index number of Vave_abs_diff(i) at which Vave_abs_diff is zero.
This index number shall I then use to send k(i) back to the sheet.

Hope you or anyone else has some useful ideas.


Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo Theend
'
Dim input_cell_1 As Single, input_cell_2 As Single
Dim help_var1 As Double, help_var2 As Double
Dim Cal_cell As Double
'
Const k_start_val As Single = 1
Const k_stop_val As Single = 4
Const k_step_val As Single = 0.01
'
Dim k(1 To ((k_stop_val - k_start_val) / k_step_val)) As Single
Dim Vave_abs_diff(1 To ((k_stop_val - k_start_val) / k_step_val)) As Double
Dim i As Integer
'
'
If OptionButton3 = True And Intersect(Target, Range("Input_Cells")) Then
MsgBox "I'm running_option_3"
'
'* Brug formlen for udregning af shape parameteren med parameterne
'A & Vave som kendte. Placer den udregnede størrelse i relativ ref. A1.
'
input_cell_1 = Range("input_cells").Range("b1")
input_cell_2 = Range("input_cells").Range("c1")
'
For i = 1 To ((k_stop_val - k_start_val) / k_step_val)
If i = 1 Then
k(i) = k_start_val
Else
k(i) = k(i - 1) + k_step_val
End If
'
help_var1 = Application.WorksheetFunction.GammaLn(1 + (1 / k(i)))
Cal_cell = input_cell_1 * Exp(help_var1)
Vave_abs_diff(i) = Abs(Cal_cell - input_cell_2)
'
ThisWorkbook.Worksheets("Power_curves").Range("e3:e500"). _
Rows(i).Value = Vave_abs_diff(i)
Next
'
End If
'
Theend:
Application.EnableEvents = True
End Sub


Regards :)
NCO
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Not suire if I quite follow what you're doing but you could run it through a loop, for example:

Code:
For x = 1 To UBound(Vave_abs_diff)
If Vave_abs_diff(x) = 0 Then
'Your Code Here
End If
Next x

or you could write another array with the values, something along the lines of:

Code:
For x = 1 To UBound(Vave_abs_diff)
If Vave_abs_diff(x) = 0 Then kcount(x) = x
Next x
 

Watch MrExcel Video

Forum statistics

Threads
1,123,057
Messages
5,599,550
Members
414,316
Latest member
ExcelLee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top