access a cell in a range

I have variables:

dim myrange as Range
dim myString as String

myrange specifies a specific column.  How can I specify the cell in row
1 of myRange so I can set it's contents to myString?

0
10/7/2005 3:05:09 PM
excel 39879 articles. 2 followers. Follow

5 Replies
457 Views

Similar Articles

[PageSpeed] 5

Hi there keyser soze,

(Fan of the movie, eh?  ;)  <g>  )

Like so ..

    myString = myRange(1).Value

HTH

-- 
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)


<keyser_Soze@usa.com> wrote in message 
news:1128697509.320303.12550@g49g2000cwa.googlegroups.com...
>I have variables:
>
> dim myrange as Range
> dim myString as String
>
> myrange specifies a specific column.  How can I specify the cell in row
> 1 of myRange so I can set it's contents to myString?
> 


0
firefytr (112)
10/7/2005 3:48:45 PM
yes, I liked the movie.

Shouldn't that be 
myRange(1).value = myString

?

Thanks again

0
10/7/2005 5:03:02 PM
Not if you want to set the contents of your variable to the cell value, as 
you stated.

-- 
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)


<keyser_Soze@usa.com> wrote in message 
news:1128704582.016504.176920@g49g2000cwa.googlegroups.com...
> yes, I liked the movie.
>
> Shouldn't that be
> myRange(1).value = myString
>
> ?
>
> Thanks again
> 


0
firefytr (112)
10/7/2005 11:00:18 PM
I'm sorry, I thought I replied to this, not sure what happened..

If you're looking to set your variable value from this, then no, you don't 
want to do it that way.  It would be ...

    myString = myRange(1, lngColumn).Value

Where lngColumn is a variable (either input by hand or used as an actual 
variable) which specifies your desired column.  If you are wanting to set 
the desired cell as a range variable and not as a string varible, it would 
look like this ...

    Set myRange = Cells(1, lngColumn)

Same principal applies to the lngColumn.  Substitute a column number if 
you'd like, or use a variable.

-- 
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)


<keyser_Soze@usa.com> wrote in message 
news:1128704582.016504.176920@g49g2000cwa.googlegroups.com...
> yes, I liked the movie.
>
> Shouldn't that be
> myRange(1).value = myString
>
> ?
>
> Thanks again
> 


0
firefytr (112)
10/14/2005 5:20:20 PM
Okay, well I'm a dink.  I now see my reply.  <doh!>

-- 
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)


"Zack Barresse" <firefytr@vbaexpress.com> wrote in message 
news:eC7WNOO0FHA.2924@TK2MSFTNGP15.phx.gbl...
> I'm sorry, I thought I replied to this, not sure what happened..
>
> If you're looking to set your variable value from this, then no, you don't
> want to do it that way.  It would be ...
>
>    myString = myRange(1, lngColumn).Value
>
> Where lngColumn is a variable (either input by hand or used as an actual
> variable) which specifies your desired column.  If you are wanting to set
> the desired cell as a range variable and not as a string varible, it would
> look like this ...
>
>    Set myRange = Cells(1, lngColumn)
>
> Same principal applies to the lngColumn.  Substitute a column number if
> you'd like, or use a variable.
>
> -- 
> Regards,
> Zack Barresse, aka firefytr, (GT = TFS FF Zack)
>
>
> <keyser_Soze@usa.com> wrote in message
> news:1128704582.016504.176920@g49g2000cwa.googlegroups.com...
>> yes, I liked the movie.
>>
>> Shouldn't that be
>> myRange(1).value = myString
>>
>> ?
>>
>> Thanks again
>>
>
> 


0
firefytr (112)
10/14/2005 5:35:04 PM
Reply:

Similar Artilces:

Can't open shared access database with double click from explorer.
While trying to diagnose Access 2007 corrupting Access 2000 databases, I've come across a situation where if I try to double click an MDB file on a shared network drive, nothing happens. Access doesn't start (either with access 2000 or access 2007). However, if I wait til I get the "Open File - Security Warning" on one machine, then double click it on the other, then I can open it on both machines. In addition, I can always open it from the open menu in access. Obviously something to do with the open file warning is going on here. Is there any way to set the regist...

How to Filter cells and save the file with certain criteria?
Hello. I have a Excel file that contains a large list of Tracking Numbers. The tracking numbers are from two sets of order types- Internet orders and Mail Orders. Orders that are from the Internet are matched up with a order number such as "5678". Orders from the mail-order side are designated by initials "MO". So in Excel it'll look something like this: trackingnumber orderid 123365656666 5467 152155896345 5468 123365634567 MO 152134567789 5469 152151234563 MO Is there a way to eliminate the Mail-Order rows and save the Excel file to ...

Microsoft Access Subform Question
Group, I have a Ms Access 2000 database with four tables, One primary (NAMES), sub tables are PAYMENT, REFERRED AND PURCHASE. I want to reference the Totals field from the table PAYMENT in the unbound text box called txTotalPayment. This is in a form called CUSTOMERS with a subform called PAYMENT. What formula (if thats the correct name for it) do I use to reference this data? http://www.webguytx.info/access/relationship.png (shows table relationship) Robert Hello Robert, If the name of the subform control is [Payments] then for the ControlSource of [txTotalPayment] enter: =[Paymen...

pasting into merged cells #2
Every time I try to copy and paste data into a merged cell I get error messages about the cells not being the same size. Even if I try to just paste values only. Is there any workaround or fix for this??? Dennis Try pasting into the formula bar. It's a pain, but it works. HTH Carole >-----Original Message----- >Every time I try to copy and paste data into a merged cell I get error >messages about the cells not being the same size. Even if I try to just >paste values only. Is there any workaround or fix for this??? > >Dennis > > >. > See my res...

Creating an Excel table from Access
Hi, I have an Access database which I use to log downtime for systems. I have a requirement to produce a monthly report based on this data, however, this needs to be exported to excel in a specific format. Down the left side of the report need to go the names of the services, with the days (numerically like: 1, 2, 3 .. 29, 30 etc) across the top. Then I need to count the number of times each service was down on a give day, and insert that information into the necessary cell - so if intranet services had been down twice on 16th March, for example, there would be a 2 in column 16, whi...

cell protection to allow GROUP/UNGROUP function
2 asthetic questions for all you advanced Excel Users.... Q1. is it possible to protect a worksheet, but still be able to us the +GROUP & -UN GROUP buttons ? to expand / contract the viewe ranges? Q2. anyone know how to change the colour of the AUTO FILTER button - i changes from black to dark blue when activated - i would like to hav this with more contrast and so it jumps out at you ( and the othe plebs in the office who keep forgetting) Regards David ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages dire...

Does Access work properly under Vir PC?
Hi Can you develop access db's under Vir PC? I could not use it with my old ver of Vir PC MS Access works fine (although slowly) in virtual PC. -Jim Gordon Mac MVP All responses should be made to this newsgroup within the same thread. Thanks. About Microsoft MVPs: http://www.mvps.org/ Search for help with the free Google search Excel add-in: <http://www.rondebruin.nl/Google.htm> ---------- In article <047501c3cee4$fbf13460$a001280a@phx.gbl>, "Tom" <gamedayphoto@yahoo.com> wrote: > > Hi > Can you develop access db's under Vir PC? > I could...

Highlight Cells with different values
Hi, I have a column that I want to locate the differences. I can use the F5---Special---Column Differences. But I wanted to know if I can do it with Conditional Formatting. Like if Value in A1 is different than the values in Column A:A, then it is highlighted... Any help appreciated Fuad Sounds like an Excel question..try one of the Excel newsgroups, this one is for Publisher. "Fuad" <Fuad@discussions.microsoft.com> wrote in message news:0536FB95-362E-412F-BD1C-99BF9B10E8CD@microsoft.com... | Hi, | | I have a column that I want to locate the differences. I can use th...

How to total cells in a range with data input
Greetings to all members I am running Excel 97. An office colleague presented me with an issue that might be of interest to any Excel whiz and seasoned programmers. I would certainly appreciate any pointers in solving it. So here goes. The set of values includes 33 cells ranging from B4:D14. The data type is numeric. Data is only input in a few cells. QUESTION What would be the function to enter in, say, cell A15 to indicate the total number of cells in range B4:D14 that have received data input? Or does it involve some behind the scene VBA programming? EXAMPLE B6 = 9 C9 = 4 D11 ...

How do I protect one single cell?
I have a spreadsheet where people are going to type in numbers, and the final cell is a total, which of course I don't want people to accidentally type over, as it will lose the formula behind it. So...... I put some "data validation" behind that cell, to say only allow entries of text length = 50 - a kind of "mock" condition. Nobody'e ever gonna type in 50 chars. are they?! And sure enough, typing in (eg) 854221 brings up the error box to stop them, just like (eg) HHJSYT brings up the error box to stop them. I even unchecked the ignore blank" box, so that ent...

converting Access 2000 to Oracle/SQL Server
Could anyone give me instructions or point me to an article on how to convert Access 2000 tables to Oracle or SQL Server? Any help would be greatly appreciated. Michael. "Michael" <mdubin@hotmail.com> wrote: >Could anyone give me instructions or point me to an >article on how to convert Access 2000 tables to Oracle or >SQL Server? Any help would be greatly appreciated. Do a search at the Knowledge Base at support.microsoft.com using the keywords "upsizing" to review the various white papers on upsizing Access to SQL Server as well as to ensure you h...

Creating a word2000 document with access 2000 data
I have a database in access 2000 it is basically customer info. address and dates and timesof appointments. I need to place a button on the formpage that prints a letter using the customer details and the date & time entered in the database. Anyone have any clues as to the easiest way to do this.sort of done it using mailmerge but not what I want. Actually want a word document to pop up that the staff can just double check and press print. Any help would be appreciated even just a point in the right direction to a good source of information. to see if the following website's offer...

Windows 2008
Hi, I am an admin in windows 2008 server. However, I am not able to access the c:\Users\<user name>\Local Settings\History Anyone knows how to change the settings so I can access the folder? Thank you, "Jack Black" <gwklocker01@gmail.com> said this in news item news:60b578d9-ce73-4ab8-851b-9039ea6cfee5@n16g2000yqm.googlegroups.com... > Hi, > > I am an admin in windows 2008 server. However, I am not able to > access the > c:\Users\<user name>\Local Settings\History > > Anyone knows how to change the settings so I can...

Using OUTPUT TO in VBA, then how to modify the XLS from Access?
I'm using the simple "OUTPUTTO" command to send a query over to Excel, however we will have many users and we would like to programmatically add the header rows, date printed, turn on the auto filter and freeze panes, all of which I can do within a Macro in Excel, but how do I call that macro to run from Access and How to run it on the file that they just created? Current Code: DoCmd.OutputTo acOutputQuery, "MainRptWUser", acFormatXLS, , True "ThriftyFinanceGirl" wrote: > I'm using the simple "OUTPUTTO" command to send...

Question on LABEL Range
In a LABEL Range(say that it has 12 elements row-wise), how do one refer to the 4th elements? I have this ideas that in a worksheet, I have 12 rows single column LABELED range called MONTHS, where 1st elements is JAN(that is C6), and so on, and last element would be DEC(C17). How do I get the 4th element which will be APR, and put that value in the different cell using the LABEL reference instead of the absolute cell location(C9)? Thanks I am not sure I understand your question? You refer to the labels with their names, so if you want to sum the April row you use =SUM(Apr) Otherwis...

The recalcitrant active cell.
I am way over in column EO, reconnoitring data, when I need assistance of a "what if", I activate the auto filter, and the active cell jumps back to A1. any workarounds for this annoying habit? Thanks in advance. Works for me, if I filter on something in one column and the active cell is there as well it won't change to A1 2 guesses, your active cell was A1 from the start even though you filtered on EO you have a recorded macro that was setup to go back to A1 -- Regards, Peo Sjoblom "Rodney" <rodney@touch88gum.com.au> wrote in message news:u3PI%...

Query based on multiple parameters
I would like to create a query where the user can input 2 or more parameters (part numbers), separated by comma and/or space and return information about that part. I already have a query that accepts one parameter. Is there a way to do this with more than one? I have read some posts that say to use a table to serve up the paraments and the user chooses one or more. However, a particular part or parts may not yet be in the table as they are new or unknown. Is there any way to do this without using an existing table but just accept input from the user on the fly? T...

How To Add Rows and Cells in The Table Using IHTMLTable
Hello Everyone, I am currently working on the DHTML Dialog Based Application. I have been stucked due to a problem, which is that my HTML Page contains a HTML Table element and a button, i want my application to add rows and cells on the click event of that button. I have captured the event of the button but i am unable to add rows and cells in the table. Can anyone tell me how to acheive that by using the IHTMLTable interface. Thanks in advance. From, Ghazanfar Ali. "Ghazanfar Ali" <ghazanfarali17@hotmail.com> wrote in message news:uMq8CiCjFHA.1416@TK2MSFTNGP09.phx.gb...

A Macro that will cut and paste to specific cell
I import tables from web into excel. These tables vary in size. Therefore one day the first table may go from A:7 to P:20 then the second table starts at A:22 and ends at P:40 and so on. Then the next day the first table may be A:7 to P:12 and the second A:14 to P:33. I need each table to have the same starting point each day. I have been cutting and pasting each individule table to achieve this. Is there a macro that can do this or a macro at the very least if highlight the table will then cut and paste for me to a specific cell. Thank you for any help you can give Hi, can you send me...

format cell to change color when copied
I do alot of cut and pasting. Is there a code that will change the color of a cell if I right click to copy it, so I can track what I have worked on? Thanks, Tawnee -- tawnee jamison ------------------------------------------------------------------------ tawnee jamison's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=16137 View this thread: http://www.excelforum.com/showthread.php?threadid=346063 Tawnee Can be done, but you'll soon end up not knowing which was which. Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _ Cancel As Boolean) ...

A little help with access forms.
I am in the process of converting an Access 2002 db to use SQL Server 2000 as the backend. I have found a bug that is giving me fits. I know it is because of my own ignorance as to how Access forms work. I have a form that pulls from a local query that joins two tables. I have a second form that opens when a button is pushed on the first form. The second form updates one of the tables called from the first form. After the update and I close the second form, the first form is unchanged. Even if I refresh the record or scroll to another one, the record appears to be unchanged. If I close the md...

Inserting something at the begining of evrey cell
How can I insert soemthing at the begining of evrey cell in a column? i.e. Product Name1 Product Name2 Product Name3 Product Name4 Product Name5 into Something Product Name1 Something Product Name2 Something Product Name3 Something Product Name4 Something Product Name5 As always, TIA, Jav ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com try this Sub addtext() For Each c In range("i4:i24") If Len(c) > 0 Then c.Value = "add " & c Next End Sub ...

Getting current time into cell as a static value
I want to get the current time into a cell either by clicking in the cell or clicking a pushbutton. Kind of like using =NOW() but once the value should not change after it is initially set. A bit like using Ctrl-Colon but as the spreadsheet will be run on a PDA it needs to be activated by clicking in the cell or a pushbutton. Thanks Ctrl-Shift-Colon? <wadefleming@yahoo.com> wrote in message news:1126044763.859134.103990@o13g2000cwo.googlegroups.com... >I want to get the current time into a cell either by clicking in the > cell or clicking a pushbutton. > > Kind of like ...

adding up some cells
I would like to know if someone could give me a formula that will add up some cells? On a weekly time sheet if I put an "X" in a cell by each day for each x if would add $6.00 for parking. So at the end of a 5 day week it would show $30.00 in one cell and if there was only 4 "x"s it would show $24.00 in a total cell. Mon, 8 hours, "x" /// Tues, 8 hours, "x" and so on.... The X's would be in cells- F8, J8, N8, R8, V8, Z8, AB8 total shows in AF8 Each x = $6.00 I hope this makes sense, Thanks for your time Wayne hi Wayne, =SUMPRODUCT(--(F8=&quo...

hyperink outlook mail messages to cells in excell
can anyone tell me how to hyperlink a mail message in outlook to a call in an excel worksheet I am trying to keep track of correspondence this way! ...