How to change a value in an Array

[gertsolo]

Hey,

I have an two dimensional array where I have stored some values in. (in this case the reference to Excel cells)

How do I change the value of an entry in the array.

suppose that string A36 is stored in array[0][1], how do I change that string to let's say C36?

thx,

[PsaltyDS]

Hey,

I have an two dimensional array where I have stored some values in. (in this case the reference to Excel cells)

How do I change the value of an entry in the array.

suppose that string A36 is stored in array[0][1], how do I change that string to let's say C36?

thx,

$C36 = "Wherever you got it from"

$array[0][1] = $C36

[gertsolo]

Thanks for the megafast reply.

When working with excel A1 ranges, how do I increase the column?

Once again, if I have the cell range A36 stored in the array, how do I change it to B36?

this is just an example the value could also be C55 or G67 or whatever.

thx

[aslani]

Thanks for the megafast reply.

When working with excel A1 ranges, how do I increase the column?

Once again, if I have the cell range A36 stored in the array, how do I change it to B36?

this is just an example the value could also be C55 or G67 or whatever.

thx

Assuming that A1 = $array[1][1], A2 = $array[1][2], ... A36 = $array[1][36]

Then, $array[2][36] = $array[1][36].

[gertsolo]

Assuming that A1 = $array[1][1], A2 = $array[1][2], ... A36 = $array[1][36]

Then, $array[2][36] = $array[1][36].

That's just it, I don't know what the exact value in the array is. I do a search in an excel worksheet for a certain unique value. The result is stored in an array and I know that the result is stored in $Finds[1][0].

How do I change that value with one or more columns.

If the result is C34 I want to put a certain value in the cell, for example D34 (or E34 or G34)

How do I change that C34 to for example D34 (or E34 or G34)?

Edited October 2, 2007 by gertsolo

[PsaltyDS]

Thanks for the megafast reply.

When working with excel A1 ranges, how do I increase the column?

Once again, if I have the cell range A36 stored in the array, how do I change it to B36?

this is just an example the value could also be C55 or G67 or whatever.

thx

Well, by convention, a 2D array is usually [$row][$col], so A1 would be [0][0] or [1][1], depending on if you use zero-based or one-based arrays. Assuming one-based arrays for the moment, A1 would be [1][1], A36 would be [1][36], B36 would be [2][36], C55 would be [3][55], and G67 would be [7][67].

Note: All arrays are really zero-based, but you can ignore the [0][0] cells by convention in many instances. When declaring a new array, however, the 0-elements must be counted. So, if G67 is to be the highest cell in a 2D 1-based array, you would have to declare it with: Dim $array[8][68].

To walk column 36 of all rows would be:

For $x = 1 To Ubound($array) -1 
      ConsoleWrite("[" & $x & "][36] = " & $array[$x][36] & @LF)
NextoÝ÷ Ù:0jYW(駲룷¥u·ºÚ"µÍÜ    ÌÍÞHHÈXÝ[
    ÌÍØ^KHLHÛÛÛÛUÜ]J    ][ÝÖÌ×VÉ][ÝÈ [È ÌÍÞ  [È ][Ý×HH    ][ÝÈ  [È ÌÍØ^VÌ×VÉÌÍÞH  [ÈB^

Did that help any to clarify?

Edit: Dratted forum editor bug...

[gertsolo]

It sure did PsaltyDS,

but I think I have asked my question the wrong way.

The value stored in $array[1][0] is let's say C34 but it could also easily be D67.

That was the result of a cellsearch.

How do I change that value from let's say from C34 to E34. I want to change the column from C to E, or from D to F.

[PsaltyDS]

That's just it, I don't know what the exact value in the array is. I do a search in an excel worksheet for a certain unique value. The result is stored in an array and I know that the result is stored in $Finds[1][0].

How do I change that value with one or more columns.

If the result is C34 I want to put a certain value in the cell, for example D34 (or E34 or G34)

How do I change that C34 to for example D34 (or E34 or G34)?

Are you using Locodarwin's ExcelCOM_UDF? Post the code snippet that gets you the searched for value. I suspect you can get the LOCATION of the searched for value vice the value itself, with a slightly different method. If that's what you're after.

Edit: i.e. _ExcelFindInRange() returns an array of matching cell addresses instead of the matching contents.

Edited October 2, 2007 by PsaltyDS

[gertsolo]

Are you using Locodarwin's ExcelCOM_UDF? Post the code snippet that gets you the searched for value. I suspect you can get the LOCATION of the searched for value vice the value itself, with a slightly different method. If that's what you're after.

Yup, that's the one I'm using. I'm using the _ExcelFindInRange function to get the result in an array. It is that result that I want to alter.

I want to know the matching cell address and want to alter that address so I can copy something to that address.

Edited October 2, 2007 by gertsolo

[aslani]

That's just it, I don't know what the exact value in the array is. I do a search in an excel worksheet for a certain unique value. The result is stored in an array and I know that the result is stored in $Finds[1][0].

How do I change that value with one or more columns.

If the result is C34 I want to put a certain value in the cell, for example D34 (or E34 or G34)

How do I change that C34 to for example D34 (or E34 or G34)?

Are you trying to move the value to a different cell, or you're trying to change the value of a cell?

Keep in mind that C34 IS a cell, which in an array, $array[3][34] IS a cell.

Let's assume that I understand you correctly.

You found the value "Content of a Cell" in C34. Then you saved that value in a variable you call $Find[1][0].

According to what I understand, you want to place that value in D34?

If so, then you're doing this all wrong.

What you need to do is map your spreadsheet first by assigning each cell in your spreadsheet into an array.

Step 1: Save each cell into the $array variable using PsaltyDS' example. Either making A1 = $array[1][1] (1-based) or $array[0][0] (0-based)

Step 2: Your $array should match your spreadsheet. If your spreadsheet have A1 - G34, then you should have $array[7][34] (1-based) or $array[6][33] (0-based)

Step 3: You can then manipulate the values to your heart's content.

EDIT: OR use that (Locodarwin's ExcelCOM_UDF)

Edited October 2, 2007 by aslani

[gertsolo]

Are you trying to move the value to a different cell, or you're trying to change the value of a cell?

Keep in mind that C34 IS a cell, which in an array, $array[3][34] IS a cell.

Let's assume that I understand you correctly.

You found the value "Content of a Cell" in C34. Then you saved that value in a variable you call $Find[1][0].

According to what I understand, you want to place that value in D34?

If so, then you're doing this all wrong.

What you need to do is map your spreadsheet first by assigning each cell in your spreadsheet into an array.

Step 1: Save each cell into the $array variable using PsaltyDS' example. Either making A1 = $array[1][1] (1-based) or $array[0][0] (0-based)

Step 2: Your $array should match your spreadsheet. If your spreadsheet have A1 - G34, then you should have $array[7][34] (1-based) or $array[6][33] (0-based)

Step 3: You can then manipulate the values to your heart's content.

EDIT: OR use that (Locodarwin's ExcelCOM_UDF)

mmm,

maybe I'm not explaining myself to good, my bad guys.

let me break it down.

I have a spreadsheet with a bunch of columns with all the dates for each month.

I want to put a certain value next to each date.

What I do is a search for the correct date, I store that result (the cell location) in the array with the excelsearch.

Then I want to alter that value so I know where I have to copy the stuff I want to enter in the sheet.

The value stored in the array IS the cell address not the content I want to put in the cell.

Hope this clarifies a bit.

[aslani]

mmm,

maybe I'm not explaining myself to good, my bad guys.

let me break it down.

I have a spreadsheet with a bunch of columns with all the dates for each month.

I want to put a certain value next to each date.

What I do is a search for the correct date, I store that result (the cell location) in the array with the excelsearch.

Then I want to alter that value so I know where I have to copy the stuff I want to enter in the sheet.

The value stored in the array IS the cell address not the content I want to put in the cell.

Hope this clarifies a bit.

So after a search, your $Find array will have something like this?

$Find[$n_cell][$n_val] = [[$cell_location_1, $cell_value_1], [$cell_location_2, $cell_value_2], ...]

Edited October 2, 2007 by aslani

[PsaltyDS]

mmm,

maybe I'm not explaining myself to good, my bad guys.

let me break it down.

I have a spreadsheet with a bunch of columns with all the dates for each month.

I want to put a certain value next to each date.

What I do is a search for the correct date, I store that result (the cell location) in the array with the excelsearch.

Then I want to alter that value so I know where I have to copy the stuff I want to enter in the sheet.

The value stored in the array IS the cell address not the content I want to put in the cell.

Hope this clarifies a bit.

The result location array returned by _ExcelFindInRange() gives you the location in more than one format, you just pick one and use it:

Quoting the header in ExcelCOM_UDF for _ExcelFindInRange():

; Return Value(s): On Success - Returns a two dimensional array with addresses of matching cells. If no matches found, returns null string

; $array[0][0] - The number of found cells

; $array[x][0] - The address of found cell x in A1 format

; $array[x][1] - The address of found cell x in R1C1 format

; $array[x][2] - The row of found cell x as an integer

; $array[x][3] - The column of found cell x as an integer

So, if you want the location in A1 format it's in [x][0], if you want R1C1 format it's in [x][1], if you want the row and col as separate numbers they are in [x][2] and [x][3], respectively. Use the format you need and ignore the rest.

Locodarwin was pretty clever with his function inputs so _ExcelWriteCell() will accept any of those three formats to specify where to write:

Quoting the header in ExcelCOM_UDF for _ExcelWriteCell():

;===============================================================================

;

; Description: Write information to a cell on the active worksheet of the

; specified Excel object.

; Syntax: _ExcelWriteCell($oExcel, $sValue, $sRangeOrRow, $iColumn = 1)

; Parameter(s): $oExcel - Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()

; $sValue - Value to be written

; $sRangeOrRow - Either an A1 range, or an integer row number to write to if using R1C1

; $iColumn - The column to write to if using R1C1 (default = 1)

; Requirement(s): None

; Return Value(s): On Success - Returns 1

; On Failure - Returns 0 and sets @error on errors:

; @error=1 - Specified object does not exist

; @error=2 - Parameter out of range

; @extended=0 - Row out of range

; @extended=1 - Column out of range

; Author(s): SEO <locodarwin at yahoo dot com>

; Note(s): None

;

;===============================================================================

[gertsolo]

I hear ya loud and clear,but...

and really sorry for me being so slow or posing my question badly...

When I get the result, how do I alter it a bit? At the moment I'm working in A1 Format. Let's say the result of the search was D35. How do I alter it so it becomes F35 before I use it with _ExcelWriteCell():

cheers!

[aslani]

I hear ya loud and clear,but...

and really sorry for me being so slow or posing my question badly...

When I get the result, how do I alter it a bit? At the moment I'm working in A1 Format. Let's say the result of the search was D35. How do I alter it so it becomes F35 before I use it with _ExcelWriteCell():

cheers!

Why do you need to alter it if you can do;

_ExcelWriteCell($oExcel, "Some Value", 35, 6) ; F = 6

Per your example, if you found the date you want to edit in D35, why do you want to write in F35?

[PsaltyDS]

I hear ya loud and clear,but...

and really sorry for me being so slow or posing my question badly...

When I get the result, how do I alter it a bit? At the moment I'm working in A1 Format. Let's say the result of the search was D35. How do I alter it so it becomes F35 before I use it with _ExcelWriteCell():

cheers!

You didn't specify the transformation used, but assuming from your example that it's just row+2, I would not use A1 format from [x][0]. Use the row and col numeric values in [x][2] and [x][3].

So it becomes: _ExcelWriteCell($oExcel, "New Data", $Find[$n][2] + 2, $Find[$n][3])

[aslani]

I hear ya loud and clear,but...

and really sorry for me being so slow or posing my question badly...

When I get the result, how do I alter it a bit? At the moment I'm working in A1 Format. Let's say the result of the search was D35. How do I alter it so it becomes F35 before I use it with _ExcelWriteCell():

cheers!

After re-reading your post I think I now know what you're trying to do.

Example:

; this is the breakdown of your $Find array
$Find[0][0] = 1
$Find[1][0] = D35 ; A1 Format
$Find[1][1] = R35C4 ; R1C1 Format
$Find[1][2] = 4 ; Column
$Find[1][3] = 35 ; Row

$add_string = "My Birthday"
$get_date = _ExcelReadCell($oExcel, $Find[1][3], $Find[1][2])

_ExcelWriteCell($oExcel, "" & $get_date & @CRLF & $add_string, 35, 6)

I hope that's it.

[PsaltyDS]

After re-reading your post I think I now know what you're trying to do.

Example:

; this is the breakdown of your $Find array
$Find[0][0] = 1
$Find[1][0] = D35 ; A1 Format
$Find[1][1] = R35C4 ; R1C1 Format
$Find[1][2] = 4 ; Column
$Find[1][3] = 35 ; Row

$add_string = "My Birthday"
$get_date = _ExcelReadCell($oExcel, $Find[1][3], $Find[1][2])

_ExcelWriteCell($oExcel, "" & $get_date & @CRLF & $add_string, 35, 6)

I hope that's it.

Row first, then column. Row is in [x][2], column in [x][3]. Also reversed in your _ExcelWriteCell().

[aslani]

Row first, then column. Row is in [x][2], column in [x][3]. Also reversed in your _ExcelWriteCell().

That might be a discrepancy since _ArrayDisplay() shows me the other way around.

[gertsolo]

You didn't specify the transformation used, but assuming from your example that it's just row+2, I would not use A1 format from [x][0]. Use the row and col numeric values in [x][2] and [x][3].

So it becomes: _ExcelWriteCell($oExcel, "New Data", $Find[$n][2] + 2, $Find[$n][3])

Yup, that's it, so darn easy,

thanks a lot everybody for the help.

Going for my next challenge (I so Luv autoIt)