Search:

Hi all,

I've been looking for some VBA which checks to see if a worksheet name already exists. I found the code below on a different website (yes VB Forum I've been cheating on you...):


...

That's great, thanks Gruff. :)

Hi All,

I created a macro to automate some repetitive tasks at work. A part of the process takes a list of asset names and one at a time creates a worksheet with that name, and does some...

Excel 2010 Question:

I have a 2 dimensional variant array which is created dynamically by reading in a range from a worksheet:


BurstArray = ws.Range(ws.Cells(4, 15), ws.Cells(LastRow, 17))
...

That's great, thanks passel!

One final thing which would make it perfect - is there any way to retrieve the current value of the last two parameters? So that when the userform opens the comboboxes...

I have a file which is a text-type file - it doesn't have a .txt extension it has .cpxparam, which is basically a text file but relates to some parameters for some linear optimisation (don't ask)...

I'm currently using Excel VBA to update a SQL Server DB on a hosted environment for a corporate-level database (holds raw data for some financial models we are building).

The SQL I am building in...

I have a userform that once open has a button. I need some VBA code that, when the user clicks the button, it populates a text box with the result of a formula.

Basically I need to following: User...

I currently have a list of references in a single column. Typically there are about 5,000 entries in the list. There is also a Master list which contains about 250,000 records.

What I need to do...

Ah yeh of course, sql obviously has no way of knowing what those variables are...doh! Should have realised that. :-/

I've used your first solution as it's something I know how to implement. One...

I have created a database connection between Excel VBA and SQL Server. I've been trying to use the INSERT INTO function to add records to an existing table. I won't post all the code as the...

Cheers guys :-)

I have a macro that allows the user to select a range and the selection is passed to a Range variable. However I need another variable that is automatically assigned the FIRST (upper-left) address of...

Sorry to re-open this but I've thought of something that would be a useful addition. I've got the code working but I'd like to be able to have a message box appear at the end stating how many...

Cheers guys, great responses as always. :-)

Regards
-Rob

Hi West,

Thanks for the answer. Storing them in a standard module is fine if this is the best available approach. The fact they will go out of scope once the workbook is closed is fine, they only...

Hi all,

I am creating a VBA userform in Excel that allows the user to select a range and then perform some calculations on it. The userform needs to "remember" if a range has been previously...

I have a 2-dimensional variant array which reads in a defined range, each cell of which contains string data. Is there a way to iterate through the array and have VBA search each element for a...

That's great, thanks. :-)

I'm trying to populate a variant array with a defined range from a defined sheet, e.g.



Dim Facilities() As Variant
Facilities = Sheets("FacilityDefinitions").Range("A2:A55") 'populate array...

Okay one final set of questions/issues. I have used a class and collection to group instances of the class as follows:

Simple Employee Class in a Class module:

Private psurname As String
...

Si, Westconn, sorry I wasn't ignoring Si's post I was actually tinkering around with the Collection object as they suggested. I got it working in a similar fashion to the code you posted Westconn so...

Thanks for the reply...I'm not sure I understand but is this how your code works: create an array NewEmployee() and then create objects and add them to the array? Does each object have a number of a...

I don't know if it matters or not. I just can't explictly use names in the VBA code, or know how many objects I need to create. The information is in the worksheet and this is used to create and...

All,

I am trying to create a subroutine which iterates through a list of attributes and creates a range of new objects based on the data in the range. For ease of explanation I have created an...

I may have forgotten to copy across the headers... :blush:

Just noticed there is an issue with your solution if you have less than 10 columns of data...in my version if you enter 0 into row 2 of the unwanted column(s) you can just ignore the output generated...

Thanks vb...actually I think the ideal solution would be to have a custom macro on a tab that opens a dialogue box and allows you to select a range, and then an output range (or start cell) for the...

This is a permutations matrix builder that takes input from up to 10 columns and then outputs a list with every possible combination of the input data.

I originally tried to find a spreadsheet...

Cool, thx. :-)

I'm trying to pass the name of the currently active sheet to a variable of type Worksheet. I thought the following code would work but it returns a "Run-time error '424': Object required" error. Does...

In a normal VBA module I have the following (as an example):



Sub Call_Function_Test
Dim lngNumber as Long
lngNumber = WorksheetFunction(10)
MsgBox (lngNumber)
End Sub

Thanks for the reply, comprehensive answer there and some good ideas I think!

I actually found a simpler solution to my immediate problem, so simple that I was kicking myself afterwards for not...

I have a spreadsheet which stores client details in a typical columns/rows fashion, i.e. Col A = client ID, Col B = client name, Col C = client type

The worksheet with this data on is accessed by...

Sorry for the confusion, I didn't think I'd explained it that well!

The solution I was after was (supplied on another forum):



For i = LBound(arrVariant, 2) To UBound(arrVariant, 2)
...

Can't get this to work if I use Cell keyword rather than Range.



Dim arrVariant() As Array
'add some data to arr
Dim i As Long

For i = LBound(arrVariant, 2) To UBound(arrVariant, 2)

Hi TinMan,

Thanks for the response, I'll go over the code you suggested soon as it looks very useful.

Someone on another forum recommended using DateAdd too but found it still has a similar...

Okay maybe I should ask a slightly different question:

If I have a variable declared as a date and there is a date & time assigned to it, how do I increment that variable by EXACTLY 1 hour?
...

Hi,

No it's definitely not just a display issue. I've added a line graph to show the consecutive date and time. It shows that for the very first hour of a new day, the variable tracking the date &...

Hi si,

Thanks for the suggestion. I have uploaded a copy of the spreadsheet so people can see the problem for themselves. I'm not sure what's going on with it, it's like Excel is saying 1.9 + 0.1...