Sign in to follow this  
Followers 0
gertsolo

How to change a value in an Array

24 posts in this topic

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,


The more you learn, the less you know.

Share this post


Link to post
Share on other sites



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

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

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


The more you learn, the less you know.

Share this post


Link to post
Share on other sites

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].


[font="Georgia"]Chances are, I'm wrong.[/font]HotKey trouble?Stringregexp GuideAutoIT Current Version

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

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 by gertsolo

The more you learn, the less you know.

Share this post


Link to post
Share on other sites

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... :)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

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.


The more you learn, the less you know.

Share this post


Link to post
Share on other sites

#8 ·  Posted (edited)

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 by PsaltyDS

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

#9 ·  Posted (edited)

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 by gertsolo

The more you learn, the less you know.

Share this post


Link to post
Share on other sites

#10 ·  Posted (edited)

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) :P

Edited by aslani

[font="Georgia"]Chances are, I'm wrong.[/font]HotKey trouble?Stringregexp GuideAutoIT Current Version

Share this post


Link to post
Share on other sites

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) :P

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 more you learn, the less you know.

Share this post


Link to post
Share on other sites

#12 ·  Posted (edited)

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 by aslani

[font="Georgia"]Chances are, I'm wrong.[/font]HotKey trouble?Stringregexp GuideAutoIT Current Version

Share this post


Link to post
Share on other sites

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

;

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

:)

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

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!


The more you learn, the less you know.

Share this post


Link to post
Share on other sites

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? :)


[font="Georgia"]Chances are, I'm wrong.[/font]HotKey trouble?Stringregexp GuideAutoIT Current Version

Share this post


Link to post
Share on other sites

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])

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

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. :)


[font="Georgia"]Chances are, I'm wrong.[/font]HotKey trouble?Stringregexp GuideAutoIT Current Version

Share this post


Link to post
Share on other sites

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().

;)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

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. :)

[font="Georgia"]Chances are, I'm wrong.[/font]HotKey trouble?Stringregexp GuideAutoIT Current Version

Share this post


Link to post
Share on other sites

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)


The more you learn, the less you know.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0