Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
 
  HOME    TRACKER    Visual Basic




Assign Values To List Of Named Ranges


I have an excel application that has a couple of hundred defined names hold a single piece of data. All these pieces of data come from values entered into userforms.

for examlple
Range: A1
Named Range: "data1"
Value: True

Some values are strings, some are numbers, etc.

I would like to use the application with several sets of data and store the data in another workbook that could be loaded.

Here is what I need and I haven't been able to get work in VBA.

Set up 3 colums with the following information in each column.

worksheet, range name, value
sheet1 , data1, True
sheet1 , data2, False
sheet1 , data3, 7
sheet2 , data4, 1,200


I need a routine that will loop through the list and assign the value to the correct named range.

Any help is appreciated.

Thanks.

david




View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Help With Named Ranges
Hi, i wonder if anyone can help me.

I am working on a spreadsheet (which can be found here: http://www.alc.34sp.com/files/Level_3_Action_Plan.xls) and i need to add cells to a named range.

If you take a look at the sheet, you will see if you click on the cell next to date formulated, it pops up a calendar form. Now, if you click on add a review, it adds some cells in. What I need to do is add the new cell "Click here to add date" to the named range called InfoCalendarRange.

Can anyone give me a hand with doing it?

Named Ranges In Drop Down Box
Hi, I'm currently producing a VB/Excel system for my AS Level ICT......but, I've gotten unfortunately stuck.


I want to give the user the ability to "load data".
That is, a drop down menu, shows them all of the current Named Ranges-
they select one, and then press a button, that button then copies that range, and pastes it into the current area.

I thought this would be easy, but I was evidently wrong....



The first thing I tried was to make a combo box (on a worksheet) and then link a macro to the VLOOKUP cell.

That macro looked like this


Code:
Sub
Application.Goto Reference:=RefersToG12
End Sub
(Shown without the copy/past parts)

This just returned the error "Invalid Reference", and now I'm stuck
This method may be completely incorrect, so don't fixate on it
I'm open to whatever suggestions there are

Thank you for any help you may be able to provide

Looping Through Named Ranges
how do i loop through ranges that are already named?

for example.
I have four ranges in exell. I would like code that would increase the size of each range by one column.

The resizing is nt a problem( i dont think!). But how do i name a collection of all named ranges to loop through.

Excel VBA - Named Ranges
I am trying to iterate through all of the named ranges in an excel workbook, display them in a form of some type, and finally allow the user to select one of them, then copy that selected named range (defined name) and paste it into a new workbook.

I have figured out how to create a workbook. But, how do you get the names of all the named ranges in an excel workbook, and how do you copy? I can copy a range of cells, but I need to copy using the defined name...

THanks!

Named Ranges In Excel To Access???
hello,
i have an excel spreadsheet that has these headings

Date| WeekEnded| BlankRow | ProgramID | ProgramName | DailyActivities |ProgramActivities| Hours |

i currently have a database table with the same fields.. i used a named range (=Input!$A$9:$H$29) in excel to define my data and then put it into the table and it works fine...

here is the problem.. i dont want to include "programname" in the table but i do want it in the excel spreadsheet i tried to use a named range skipping that field in excel (=Input!$A$10:$D$29,Input!$F$10:$H$29) ... now when i execute my code ...(DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "tblData", F, True, "Test") ...it says it cant find the object 'test'

is there a reason why i couldnt use a selected range like that?

thanks for the help

winxp pro
office 2003

Named Ranges - Resizing How? (Excel,VBA)
How can i resize a named range in Excel.

I have several functions that refer to ranges, so that when I run my code I don't have to rewrite the functions to the cell if the size of the ranges change. ...... well that's the idea ... except I can't seem to figure out how to resize a named range

Can I Use Dynamic Named Ranges In Code?
I have created a dynamic named range using this formula
=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),COUNTA(Data!$1:$1))

the name of the range is rptData.

Is there a way I can reference this name from code. I keep receveving an error.

Referencing Named Ranges On Seperate Sheets
i have a named range (MyRange) that i use to populate a combo box on the same page.

i want to move the named range to another page. how do i reference it?

i've tried NewPage!MyRange in the ListFillRange property which i had previously assigned simply MyRange but that doesn't work...

How do i refer to a named range on another sheet?

Excel Sorting Range Of Named Ranges
I'm modifying another developers work, and of course he's out on vacation.

My problem is he has a worksheet with multiple columns.
He creates each record in the spreadsheet with vba code, and gives the range a name based on an account number ie. if the account number is 9249 he names the range r_9249 and that range is all of the columns in that specific row.

Now the problem is, if you set the range to all of the existing rows and sort it.. the data sorts fine but it doesnt' appear that the named ranges sort

Is there anyway to force it to sort the named range along with teh data?

basically
r_123,1,aakdsfa,aslkf
r_956,4,jlknma,afoiaef
r_224,8,aslkfjs,aslfkjasf
r_555,3,asflksj,saflksjfas

Should sort as
r_123,1,aakdsfa,aslkf
r_555,3,asflksj,saflksjfas
r_956,4,jlknma,afoiaef
r_224,8,aslkfjs,aslfkjasf

note the first column doesnt' actually have a value, that is just the name of the range. from column 1 to column4

any ideas?!

Import Text File & Named Ranges
I am in a quandry ...

I have the following code in a macro which will import a text file into my excel worksheet. It works fine, however, upon each import a new named range is created. This import is executed repeatedly and the number of named ranges grow out of control. I cannot just delete all of the named ranges because there are legitimate named ranges I use. Is there a parameter to set that will not save the named range? or possibly a command to delete all imptxt* named ranges? I was told .Delete at the end would remove the named range but this does not appear to be the case.

Any help would be greatly appreciated!!! Manually deleting these will be a definite headache

Code as follows ...

improw = ActiveSheet.UsedRange.Rows.Count + 1

textfile = "C:SS est_imp.txt"

imp_textfile = "TEXT;" & textfile

If Len(Dir(textfile)) = 0 Then

MsgBox " There Are No Jobs To Import ! "

Else

With ActiveSheet.QueryTables.Add(Connection:=imp_textfile,
Destination:=Range("A" & improw))
.Name = "imptxt"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertEntireRows
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.Refresh BackgroundQuery:=False
.Delete
End With

Named Ranges After Copying Sheets From Another Workbook
I may be going about this in a very non-conventional way, but I'm having problems doing some data input/output in Excel.

The overall goal of the program will be to function as a customer worksheet generator / viewer. The data from the users gets stored in a series of named cells on many different worksheets. When the user hits the save button, I have the program create a new workbook and copy all of the "raw data" sheets over to the new workbook.

When a user goes to open a customer's info, the program finds the appropriate "raw data" workbook and does two things:
1. It deletes the blank sheet from the application
2. It copies the populated ("raw data") sheet into the application's workbook so it has the same name as the original copy.

After step 2, however, none of my named ranges work. When I refer to one in my code (i.e. range("cellACRO").value ) I get a "Method Range of Object _global" error. If I select the sheet that contains the named range first, however, the program works fine.

Does anyone have any thoughts on this?

Thanks
-Chris

How To Prevent Excel From Modifying Named Ranges?
I've got a situation on my hands where Excel is trying to be "helpful", and it's making my functions blow up. I have a particular named range that must remain the same, no matter what happens to the worksheet. However, when rows are inserted into this worksheet, Excel modifies the range's formula for me, even if it's declared with absolute references.

F'rex, create a new worksheet, throw some numerical values into column A from row 12 downward, and define a name with the following formula:
Code:
=OFFSET(Sheet1!$A$12, 0, 0, IF(COUNT(Sheet1!$A:$A) > 0, COUNT(Sheet1!$A:$A), 1), 1)
Select any cell in row 12 and insert a row, then select the range.

It'll start on row thirteen.

If you check, sure enough, Excel will have modified the formula for the range so that it starts on A13 instead of A12. I need it to not do this.

- David Prokopetz.

Formula Protection With Local Named Ranges
Hi everybody,

I'm going to describe you as proper as I can my problem.

I have in a Worksheet (call it RefSh) some formulas which refer to specific local named ranges. For example, In my RefSh, we can have in a specific cell Sh1!Name1 + Sh2!Name1.

NB1: Sh1 and Sh2 are 2 worksheets belonging to the same workbook where RefSh is defined.
NB2: In both Sh1 and Sh2 we define Name1, there is no mistake above, they have the same name.

For some reasons (my own !) I need to delete Sh1, copy Sh2, rename this copy (SH2(2)) as Sh1. The reason I want to do that, comes from the fact that Sh1and Sh2 are very similar (almost the same), and when I need to do some updates, I do it only Sh2 (beleive me it's faster) so that I duplicate it and rename it as Sh1. I hope that you follow me, because I know that it could be not really clear !

Then, when this changes are done, in my RefSh, instead of having still the formula Sh1!Name1 + Sh2!Name1, I have Sh1!Name1 + MyWorkbook_Name!Name1. And this last formula leads to a #NAME in the excel cell.

Does anybody can offer me a solution to avoid this problem ????

Thx to anyone who can take a moment to read and understand my problem, and thx a lot to anyone who can offer me a solution

G

Converting Named Ranges To Absolute References.
Using VBA for Excel, I need to do the following:
1) Locate named ranges in current worksheet.
2) Locate cells that have formulas that refer to the named ranges.
3) Convert the references (to the named ranges) to absolute references

Any help, especially with item 3 above, would be very appreciated.
Thank you

Deleteing Named Ranges From A Specific Excel Worksheet ?
Guys.

Im trying to delete all the named ranges in an excel worksheet.

Started with

Code:
Dim Names As Names
Dim i As Long

'Set Names = ThisWorkbook.Sheets("XXXXX").Names
Set Names = ActiveWorkbook.Names

For i = 1 To Names.Count
Names(i).Delete
Next i

If I try it with the specific sheet it bins out. Its fine if it wants to go and delete the lot though

Any thoughts ?

NEED EXPERT HELP: Move Data And Named Ranges To Another Sheet
How do I move only the names into a another sheet but I want them to be in a 3 columns instead of one long column? And then when you click on a name, it will display it's range a few columns over. Picture this.. a list of names and when you click on the name, all of the person's data is displayed, which IS already defined. I have the named ranges defined (insert menu > names > define). Any sample code on how to do this?

Code:
     A .. J
John Smith data
                        data
                        data
Jane Doe data
Mike K. Jordan data
                        data
                        data
                        data
   .
   .
   .




right now this is what i have displaying a list of names, except it skips the first name and i haven't been able to figure out the associate of ranges...

Code:
Sub groupTraderNamesInNewReport()
Dim r, c, d As Range, sTraderNames As String
Dim i As Long
i = 0
    Set r = Sheets("layout").Range("A4")
    Set d = Sheets("report").Range("A1")
    
   While Not (IsEmpty(r) And IsEmpty(r.Offset(0, 1)))
        Set c = r.Offset(1, 0)
        While IsEmpty(c) And (Not IsEmpty(c.Offset(0, 1)))
            Set c = c.Offset(1, 0)
            If c.Offset(-1, 0).value = r.Offset(0, 0).value Then d.Offset(i, 0).value = c.value
        Wend
        d.Offset(i, 0).value = c.value
        Set r = c
        i = i + 1
    Wend


End Sub


ATTACHED IS A PIC:



Edited by - efarhat on 8/12/2004 2:52:33 PM

Copying Excel Array Formulas With Named Ranges
Please see the attached file where I am trying to copy an array formula with a named range and it doesn't copy properly. The formula in G13 uses named ranges and it doesn't copy properly, whereas the formula in F13 uses cell references and it copies properly. Is there a solution for this?

I perfer using named ranges to recognize the variables in my formulas.

Thanks for the help.

NEED EXPERT HELP: Move Data And Named Ranges To Another Sheet
How do I move only the names into a another sheet but I want them to be in a 3 columns instead of one long column? And then when you click on a name, it will display it's range a few columns over. Picture this.. a list of names and when you click on the name, all of the person's data is displayed, which IS already defined. I have the named ranges defined (insert menu > names > define). Any sample code on how to do this?

Code:
     A .. J
John Smith data
                        data
                        data
Jane Doe data
Mike K. Jordan data
                        data
                        data
                        data
   .
   .
   .






Edited by - efarhat on 8/12/2004 7:10:44 AM

Excel, Named Ranges And Data Validation …
Hi there

I have a question on using the Named Ranges, and Data Validation, Following is a Quote form a well Known Excel Site;
“When you use the Data Validation tool, you can restrict valid inputs to the values in another range. However, the Data Validation dialog won't allow you to specify cells on another worksheet. But using a defined name, you can get around this limitation. Simply give the range containing the valid values a name, such as ValidRange, and then in the Data Validation dialog, enter =ValidRange as the Source for the validation list. Data Validation won't object, and everything will work as you expect.”
But when I do as above and try to Use my Named Range in a data Validation List In a Different Sheet It just Is not working, But the same Nmae works If I use it in the same Sheet that the Original Data Exists.

Could you tell me What is it that I am doing Wrong?

Also Is it Possible to Use Named Ranges for Data Source of a Pivot Table?

Best Regards
Sanan






Edited by - sanan on 6/15/2005 12:24:51 PM

How To Get Range Cell Addresses For Named Ranges Passed As String To VBA Functions?
Hi All

I am trying to pass an Excel range expressed as a named range (viz "MyRange") or expressed normally (viz "A1:C300") to a VBA custom function of mine as a STRING - Please take note, the Excel range is being passed, not as a range or a variant (I am encountering problems doing so), but as a STRING and hence is enclosed with double quotes.

When the same is passed in normally (viz "A1:C300") , how can I decipher the cell addresses of the top left and bottom right corner cells of the passed in range?

However, when the same is passed in as named range (viz "MyRange"), the only thing that I know is to check if there exists a named range same as the one passed in and if there is one, then probably the "RefersTo" parameter of the named range can be used to decipher the addresses of the top left and bottom right corner cells of the range.

And if that is true (for the above case), I do not know how to do use it to get it.

Can someone help me with this?


Best regards


Deepak Agarwal

How Can I Assign Serial Numbers In Multiple Ranges
I know this is not the best database practice, however, it is a business requirement.

Our orders are assigned order #s in ranges based on what product they are for, let's say products A-E, i.e.

Product A: Order #s 000000-099999
Product B: Order #s 100000-199999
Product C: Order #s 200000-299999
Product D: Order #s 300000-399999
Prodcut E: Order #s 400000-499999

I want to store all of these orders in one Orders table because they're all...orders. How can I assign these unique order #s in these different ranges?

I am using an Access 2003 Data Project using SQL Server 2000 as the backend and am fluent in VBA and SQL, I just need to be pointed in the right direction.

The other piece to this, which isn't as big a concern is that orders get entered in lump sums, so say a customer makes an order for 50 shipments, I would like to be able to assign them 50 order#s based on the next 50 consecutive #s in that range. If I can do it all at once, that would be great, but one at a time works for now, as long as they are consecutive, but by product.

I think that is clear, but I can elaborate if it will help anyone help me.


Thank you!!!

Assign Multiple Print Ranges Within A Single Excel Worksheet
Is this performed through a pagesetup function? Please get me started...thanks

Private Sub CommandButton1_Click()

Dim rCol As Range
Dim rCell1 As Range, rCell2 As Range
Dim Sht As Worksheet

'Set range variable to Selection
Set Sht = ActiveSheet
Set rCol = Sht.UsedRange.Columns(1)
'Insert page breaks
Sht.PageSetup.PrintArea =

Printing Using &"Named Ranges&"
Hello All & HNY...

I have a work sheet with multiple Form Templates on it and each template has its own print range. I have "Named" each range in accordance to the type of form that it is; E.g. the "Site Audit" template would have a print range named "Site_Audit"...and so on.

I am currently using a ComboBox to select other information that relates to the individual forms and as I do I would like to populate a cell with the "Named Print Range" and then reference that cell with the named range in it to print.

This is my latest of many attempts... I think I'm close...but still not quite there... any assistance...as always is appreciated...

Code:
'This sets the "Named Range" in cell ("A89")

With Sheets("Sheet2")
.Range("A89").Value = .Range("A57").Offset( _
ComboBox1.ListIndex - 1).Value
End With
****************************************************
'This is where I try to reference the "Named Range" in cell ("A89") to print

Dim Range As Name
Set Rng = Sheets("Sheet2").Range("A89")

With Sheets("Sheet2")
.PrintArea = Rng
.PrintOut Copies:=1, Collate:=True
End With

Thanks...

Adding One Ranges Values To Another
Hello,

I have some code that adds one range's current values to another range's values, and places tham back in the original range.

Like this:

Range("G23").Value = Range("G23").Value + Range("K23").Value
Range("G24").Value = Range("G24").Value + Range("K24").Value
Range("G25").Value = Range("G25").Value + Range("K25").Value
Range("G26").Value = Range("G26").Value + Range("K26").Value
Range("G27").Value = Range("G27").Value + Range("K27").Value

.....and so on.

I want to use one line of code to do that all at once.

like:

Range("G23:G27").Value = Range("G23:G27").Value + Range("K23:K27").Value

but this does not work. Can anyone help me?

Thanks in advance.

-Krys

Simple Question: Ranges Of Values
I have a variable that can have a value between 1 and 100. I am using this if...then...elseif...then... structure:

If myvariable < 5 then
'do this
ElseIf myvariable > 4 and myvariable < 10 then
'do that
ElseIf myvariable > 9 and myvariable <15 then
'do the other
End If

Is there a way of condensing the expression

"myvariable > 4 and myvariable < 10"

into something shorter, like:

"4 < myvariable < 10"

The above example doesn't work, but is there anything in VB that is similar??

Thanks very much,

Wicko

Assign Multiple Values
assign multiple values to a lable (or button)

Hi, I have a question. I'm new to visual basic programming and I'm stuck at something. How can I assign multiple values to a single lable. forexample, a lable has 3 values stored and when i click once i get A and if i click twice i get B three time C.. etc sorta like a mobile phone button function. How can I do it?? what functions do i need to learn to be able to do it.. thank you so much for the help. much appreciated

Assign Values To Array
I need to declare a string array which contains a certain fixed no of items. How do I assign values to a string array like in C/C++?

int arr[100]={1,2,3,4...} like this is in C

List Named Pipes & Data Bases
Hello again.

I need to list all the regested pipe lines of my machine, as well all the databases of each server. I've been trying to use the PeekNamedPipe API (or the GetNamedPipeInfo API), but it does'nt seem to work.
Is it possible to do, or is there any other solution to list servers with SQL Server?

ANA MONTEIRO

Assign Multiple Values To An Array
I'm wondering how you assign multiple values to an array at one, i think in c++ and java it's along these lines for example:

myArray() = {1,2,,3,5,2,4,7,,8}

can you do similar to this in vb on 1 line?

Assign MSHflexgrid Row Values To Textboxes
I have a MSHFlexgrid and I am trying to pass the highligted row values to several textbox that I have on the form. How would I assign the values to textboxes? Thanks

How To Assign Values To Variable Dynamically In Vb6.0
I have 20 variables like a1,a2,a3,a4,a5 .........a20.
I should assign values to variables dynamically.
for i = 1 to 20 then
if i = 1 then a1 = 10
if i = 2 then a2 = 10
next
Can anyone help me to assign values to variables dynamically.
At present i can use the above code, but it will be too much for 20 variables, i have to use 20 if's.
Anyone is there to help me to solve my problem

How To Assign Values From A Module To A Form
I have a if statement in a module that determins a value that I want to transfer the to the caption of a label in form2 but I cannot figure out how to write the code so vb knows I want the value to go with the label on form2 and not on another form. So how do you assign a value from the module to a label on a spacific form.

How Do You Assign Cursor Values In VB 2005?
I'm converting VB 6.0 code and I have the line Screen.MousePointer = vbHourglass, which is not recognized in 2005. Do you know a work around? Thanks.

Cannot Assign Values To New Datagrid Column
Hi all,

In VB6, I have a datagrid with 2 columns data fields from a database. I use coding to add a new column and it works. However, when I assign value to the new column, I got an error.

My code,

Dim ColumnCount as integer

With DataGrid1
        ColumnCount = .Columns.Count
        .Columns.Add (ColumnCount)
        .Columns(ColumnCount).Caption = "New column"
        .Columns(ColumnCount).Locked = False
        .Columns(ColumnCount).Visible = True
        .Row = 0

        .Columns(ColumnCount).Value = "new data" <-- Run time error 6147: Column not found, /1

End With

Any one knows what happened? Please help.

How To Assign Alot Of Values To Byte Array?
Say I want to assign the values 13,99,26,78 to a byte array, is there a faster way of doing it instead of like this:

ByteArray(0) = 13
ByteArray(1) = 99
ByteArray(2) = 26
ByteArray(3) = 78
etc..

How To Assign Values In Data Entry Mode
Access 2003, I have an issue with data entry. When you go to new record using the "DoCmd.GoToRecord, , AcNewRecord", all the fields go blank - waiting for you to enter data. How do I have Access assign values to 3 of those fields and let the user enter the data themselves only on the last field. The application here is the Accounts Payable clerk who receives invoices from the utility company every month and once she punches in the Store Number, a subform opens up showing the 2 or 3 meters associated with that store. When it is time to enter the billed amount for a particular Meter, I would like her to have a dropdown box that lets her choose which meter to enter and then she can enter the dollar amount manually. The problem is that each time I select a new record, all the fields go blank! When I do a dropdown box on the subform after I select new record, the dropdown has NOTHING!

Assign Values To Database Driven Combobox
Hi, in my table i have two columns name and id, i want to display name field values in combobox that i already did, my point here is how to assign values to the combobox so that if the user selects "NICK" from combobox and click on submit button i want to send the value of the "NICK" not the name. for example i have

name id
Nick 23
John 28
Sam 30

So when the user click on submit button the program should send 23, not the name itself. what i did is

Do While Not oRS.EOF
.AddItem oRS.fields(name).Value
.ItemData(.NewIndex) = oRS.fields(id).Value
oRS.MoveNext
Loop

when i'm submitting i'm getting index values like 0,1,2,3 etc...not the actual values what i have in the Database...if anybody knows please help me coz i'm new to VB

Must The Array Function Assign Values Starting At 0?
I like to dim my arrays 1 to 10, or whatever my bounds. That way I don't have to adjust for 0 being the 1st item. When I use the Array function, it assigns the given values starting at zero. Is that my only option without manually assigning to each item? I can get around this easily by putting a dummy value in for the 0th item, but this method could be a pain if my array didn't start until, say, 100.

I look forward to hearing your thoughts, ideas, and rude remarks.

Can't Assign An Image List
I have a toolbar and an imagelist.

I turned off the binding of the controls to change the pictures and add 2.

Now I can't select the imarelist in the toolbar controls properties.

Well...I can select it....but when I press ok or Apply it just goes back to none.

Anyone got any ideas?

Loop Through Named Range And Lock Range Name Based On Values
ok..basicallly I created a worksheet called 'calendar'
this calendar worksheet has 12 named ranges

rngJanuary,rngFebrary....to rngDecember(yes its a calendar)

all of these named ranges are also inside one big range called rngcalendar.

Basically I want to accomplish the following.

when the workbook get opened get the last day of the last month the user entered a value and lock every other named range after the month of the named range the last value was stored in.

eg..if on march 20th the user entered a "w" as a value I want to lock
RngApril through rngDec.

in other words force them to finish entering all values for a month before continuing to the next.

if IT helps all my days of the month are in ltgrey coloring
and the vales the enter go below the day value
in normal white cells(see attachment)


I already validate the entried in the
Private Sub Worksheet_Change(ByVal Target As Range) event but dont know how to accomplish the above step.

HELP:How Do You Specify Ranges With A Mix Of Variables And Exact Ranges
Dear All,

I am trying to specify a range that I know the start of but do not know the end...at the moment I'm doing the following:


Code:
Dim LastStartDateRow
Dim NextAfterLastStartDateRow

LastStartDateRow = Range("H" & 65536).End(xlUp).Row

NextAfterLastStartDateRow = LastStartDateRow + 1

Range("I" & NextAfterLastStartDateRow).Value = Application.WorksheetFunction.Sum("I13:I & LastStartDateRow")

This causes an error when I run it and I think its because it doesn't recognise the range in Blue.



Is the way I've defined the range wrong???Please help!!

Hi Everybody, Assign Values Of Textboxes To Textboxes In Datareport Form
how to assign values of textboxes in a form to textboxes in datareport form at run time. how can this be done.


edit by: johnminkjan : Topic Title



Edited by - johnminkjan on 12/28/2004 3:56:59 AM

List Box Values
I've searched on this forum and can't quite find what I'd like. I need a list box, doesn't matter if it's a form or a control list box, to populate it's list with a range of data. Certain sheet names are entered in column A on Sheet1 when the workbook is opened. There are several, a "SetUp" sheet, "Template", and a few more that I don't want in that list. A user can add sheets to the workbook, those will update column A the next time the user opens the workbook. All that I got to work. What I can't figure out is how to get a list box to look at the range of sheets listed in column A and populate itself with that list. As I said, the list may change occasionally, so the listbox would need to refresh it's list...

Any ideas of how I can approach this?

Thanks,

Dave

List Of Values For Sub Arg
Is there a way to allow your subs/functions in VB6 to display a list of values as in the second argument to VB functions such as msgbox etc.
ie. when you get to the second argument, the list of:
vbAbortRetryIgnore
vbCritical etc.
appears.

VB List Box Values
Hi all,

Is there code to show the value of List1, row 4??

I know listindex show a value such as "3" but i wanted the actual value of "3" like "Apple"

Thanks!!

List Values
I have a text file that looks like this:


Quote:





Dimava;not really
Jake;yes
yahoo;no






and I need to run a loop or something to set strFirstVariable as everything in that line before the semicolmn, and strSecondVariable as everything after the semicolmn in that line

thanks,

Dimava

List Box Values To CSV
I wonder if someone could help with the answer to this simple question from a VBNewbie, like me!

I have a list box where each line is made up of three concatenated strings, seperated by a vbTab. The values have been added to the list box using the SendMessage API function (if this helps!)

e.g Code:val1 & vbtab & val2 & vbtab & val3

Once all values have been added, I want to save the contents of the list box out to a CSV file - how is this done, please?. I am currently using the Write method, but my users need to import the results to Excel for analysis.

I am grateful for any pointers or code, and sorry for the easy to answer question!




Criticism comes easier than Craftmanship

Whilst I try and answer every thread there is, I find most of my answers to your posts here:

[Posting Guidelines] | [FAQ's] | [The Chosen One] | [Knowledge Search] | [Richie's Wisdom Forum]

Sum Values In List Box
How to sum the values in a list box? I am confused.

Reading List Box Values
Does anyone know how to go about saving the values in a list box into a table?

I've used code that Timbo (I believe) posted to move entries in one list box to another when they are double clicked. However, I'd like to take these selected items and save them in a table so that they can be queried / used elsewhere.

I'm as new to VBA as someone could possibly get, so any help would be greatly appreciated.

Cheers

Phil

Copyright 2005-08 www.BigResource.com, All rights reserved