Append Variable In Variable Select Case Method
Feb 16, 2012
I want the select case list of a ComboBox to be treated as a variable in order to shorten the code size. To clarify the problem, i post the code with what i want to do, but don't know how to do it that way.
Code:
Select Case ComboBox1.ListIndex
Case 0: y = "AT"
For j = 0 To 26
Case "j": y = sheets("name").Range("A(j)") 'Range A(j) is a string, so y as well, as seen in Case 0.
Next j
End Select
Is something like this possible?
View 3 Replies
ADVERTISEMENT
Apr 30, 2007
I have a userform with 3 Comboboxes, all of which need to be populated from the dropdown option before OK is pressed. I haven't used any fancy names as this is just a straightforward (or so I thought) entry form ... the UserForm is called UserForm1 and the 3 ComboBoxes are called ComboBox1, ComboBox2 & ComboBox3. I have tried to be economical with my validation routine but I cannot get an incremental variable added to the end of the word "ComboBox" to be recognised ... my code is below :
Private Sub Validate_ComboBoxes()
While MyComboCount < 3
MyComboCount = MyComboCount + 1
MyComboBox = "ComboBox" & MyComboCount
Select Case "UserForm1." & MyComboBox <<<< This fails
Select Case UserForm1.ComboBox1 <<<< This works
Case ""
MyValidCheck = MyValidCheck + 1
Select Case MyComboCount
Case "1"
MsgBox "Enter the number of weeks for this period"
Case "2"
MsgBox "You need to enter a start date"
Case "3"
MsgBox "You need to enter an end date"
End Select
End Select
If MyValidCheck > 0 Then
MyComboCount = 4
End If
Wend
End Sub
View 3 Replies
View Related
Feb 21, 2012
I have had this problem in multiple different macros so I will just provide an example of relevant code:
Code:
For J = 0 To 3
Select Case J
Case J = 0
LikeVar = "a*spk1*E0"
LikeVar2 = "a*spk1*E7"
End Select
Next J
For some reason it skips over the likevar variable assignments even though J = 0 on the first time through the loop. I have tried changing the For line to J = 1 to 3, and the case to J = 1, but it still skips to the end select. Is it not possible to use a select case on an iterator variable?
View 4 Replies
View Related
Sep 8, 2006
I need my program to:
- find the cell containing the string "Datum/Tid"
- record the column and the row of the found cell in two variables lCol and lRow
Here is my
Sub test()
Dim rFoundCell As Range
Dim lRow As Long
Dim lCol As Long
'Find method of VBA
Set rFoundCell = Range("A1")
Set rFoundCell = Worksheets("Sheet1").Range("A1:Z50").Find(What:="Datum/Tid", After:=rFoundCell, _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
'for anyof the two lines down I get the message "object variable OR block variable not set"
lRow = rFoundCell.Row
lCol = rFoundCell.Column
End Sub
View 5 Replies
View Related
Aug 28, 2006
I have a spreadsheet form with all kind of values and what I want to do is to find a data in a worksheet named "Config" based on the spreadsheet activecell value. With the find row adress I return data of another column but same row (in the worksheet) in two diferent label captions. If the spreadsheet activecell value it was found in the worksheet everything is ok but if the value is not found I'm getting the message "Object variable or with block variable not set"....
View 7 Replies
View Related
Jun 18, 2009
On my userform I have 20 comment icons (imported pictures), that when clicked need to bring up an InputBox for the user to add a comment, and store that comment in a Public variable specific to that comment which will later be written to the spreadsheet.
To keep it simple, lets say I have two comment icons to click, one to add comments to the "Testing Completed?" field, and one to add to the "Sign-Off?" field.
The first comment icon is named TestCompIcon, the second is SignOffIcon, and the public variables they write to are called TestCompComment and SignOffComment respectively.
To avoid having to code the InputBox procedure for every comment icon on the userform, I was hoping that upon click, the icon would call a centralized routine that would establish the name of the variable that needs to be written based on the name of the icon comment that was clicked. Something like as follows:...............
View 9 Replies
View Related
Oct 10, 2013
I have a reference dataset that looks like:
Adweek Start_Date End_Date
201201 05-Jan-12 11-Jan-12
201202 12-Jan-12 18-Jan-12
...
In the dataset I would like to merge the variabe Adweek, looks like:
Date Sales
05-Jan-12 $100
06-Jan-12 $110
...
15-Jan-12 $150
...
I'd like to get to here:
Date Sales Adweek
05-Jan-12 $100 201201
06-Jan-12 $110 201201
...
15-Jan-12 $150 201202
...
View 2 Replies
View Related
Dec 4, 2007
I'm trying to append a date variable to the end of headers for let's say 3 worksheets in a workbook. Each worksheet has a different string value, and I want to add a date variable at the end.
For example, sheet1, sheet2, and sheet3 says "Country Analysis", "Regional Analysis", and "State Analysis", respectively. I want to add the month and date to that so it would say something like "Country Analysis December 2007" for Sheet1, without hard coding it. Essentially, loop through however many worksheets and add a date variable at the end of each header on the left side.
I know how to get the date variable (format(date, "MMMM YYYY")) and the code behind the headers.
View 9 Replies
View Related
Aug 18, 2007
How would I be able to assign a number portion of a variable string to another string variable. For example I have the following string:
AOSXLS_356-NewInfo
I just want the 356 assigned to a string variable.
only the first 7 characters won't change. The number part could be 2 or 4 digits, and the NewInfo portion could be anything.
View 3 Replies
View Related
May 5, 2009
I am trying to sum the cells in a column from a variable starting row to a variable ending row. My problem is that I can not figure out how to use the "Range" method with variable values.
Below is what my code would look like if I was summing from I2 to I6 (hard coded)
View 3 Replies
View Related
Jan 2, 2009
I have the following
View 2 Replies
View Related
May 13, 2014
Let CopyRange = 'A' & Roll
Range(CopyRange).Select
Roll is dimensioned as Long, and is a counter for line numbers. Is this legal? I'm getting:
Method 'range' of object '_Global' failed.
View 8 Replies
View Related
Sep 29, 2006
I have 2 worksheets, one called Summary and the other called LNB. Column A in both worksheets contain account numbers.
I am trying to tell excell to look at the LNB worksheet and find the account number that is on the summary worksheet. If the account number is found, then tell me the row, on the Summary worksheet, that the account was found.
The function returns "Empty" even though I know the accounts do exist on both sheets and I dont know why.
Here is the code ....
View 9 Replies
View Related
Jun 17, 2008
If Not stfound Is Nothing Then 'if value is found then do this
is causing me a run time error 424 when I try and run my code "Object required". Entire code is pasted below...
Option Explicit ....
View 4 Replies
View Related
Oct 28, 2008
My workbook holds a month template and sheets for each month. I work on modifications in the template ,but would then like to update all the monthly worksheets. I recorded a macro to show me how to start programming the vb sub, but get a runtime failure 'error 1004 Select method of range class failed' when trying to select the column to copy,
View 4 Replies
View Related
Jun 4, 2007
I have the following code (just pasting the relevant section) which crashes when it reaches the highlighted line of code. and a dialog box pops up with the text: "Object variable or With block variable not set"
Sub test()
Dim StartRng As Range
Dim Buffer As Range
Set StartRng = WorkSheets("Sheet1"),Cells(1,1)
StartRng.Activate
ActiveCell. CurrentRegion.Select
Buffer = rngStart.CurrentRegion.Copy
' I also tried the following line of code but that didn't work either
'Set Buffer = rngStart.CurrentRegion.Copy
..
...
End Sub
View 9 Replies
View Related
Oct 15, 2009
I am trying to select rows that contain text using a variable counting number of rows so rNumb = count number of rows
The Problem
Worksheets("Table").Rows("2:rNumb").Copy
rNumb won't work. What is the proper syntax?
View 2 Replies
View Related
Jan 30, 2012
I am trying to run some code when a worksheet is selected.
I'm doing this using a macro currently (code below), but this requires that i use a quick menu button and i would like to make it a form button and assign a macro to it (so i can distribute the file without everyone having to create a menu button).
I would like to replace (or modify) the code below to select any sheet other than the ones named "a" and "b".
Code:
Sub ImportAlarms()
Dim thisSheet As Worksheet
Dim targetSheet As Worksheet
On Error GoTo failed
Set thisSheet = Application.ActiveSheet
Set targetSheet = Sheets(TARGET_SHEET)
[Code] .......
View 2 Replies
View Related
Mar 24, 2012
Basically I need a bit of VB to find the last cell in column F with text in it (is it End(xlUp)??) and then apply full borders from that cell up to cell Q5. A test macro tells me the border bit is as follows, so just need defining the range.
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
[Code] ......
View 2 Replies
View Related
Jul 10, 2007
I know how to do Range("A" & I).Select, but how about if I want columns A thru E instead?
View 7 Replies
View Related
Dec 11, 2007
I am doing is setting a variable called eof to the number of rows (with text) and i just want to select columns A1 - G1 and the eof range!
Dim eof As Integer
With Sheets("Coverage Count (%)")
eof = .Range("G65536").End(xlUp).Row
'crashes on the .select
.Range("A1:G1" & eof).Select
Selection.Sort Key1:=Range("G2"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
I've done this before and I could have sworn this worked...but i must be missing something.
View 5 Replies
View Related
Apr 17, 2008
I don't know how to concatenate a string within the Rows function. I want to be able to vary the row number with a variable, but I don't know how to construct the string, especially given that one of the delimiters within the function is ".
View 3 Replies
View Related
Jul 3, 2009
This one should be easy but I keep running into the same wall.
On every page I have single cell defined name range based on the sheet name & "_startcell"
So on sheet MfgReq I have cell A3 defined as name range "MfgReq_startcell".
I would like to the following:
.clearcontent using the "MfgReq_startcell" as the upper most left cell, all the columns to the right, and all the rows down.
on this sheet it might be 5 columns wide by 4000 row & on another sheet it might be 50 columns wide by 50000 rows.
View 9 Replies
View Related
Aug 16, 2008
I'm trying to write a macro that will validate data in variable amount of rows but will always be in a specific column. what i'd like to do is count the amount of row entries in Column A to give me "iRows." Then validate data in column E from E1-E"iRows." Currently my data validation runs for the entire column which means i can not validate blank entries. Once I can limit the validation process from E1 to E "iRows" then I can consider blank fields and mark them as invalid.
Columns("E:E").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertStop, Operator _
:=xlGreater, Formula1:="1"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Award Amount"
.ErrorTitle = "Award Error"
.InputMessage = _
"Please enter the current expected total value or current award amount for this contract."
.ErrorMessage = _
"Award amount may not be set to 0.00. If you do not have an amount awarded simply make the award amount equal to the paid amount."
.ShowInput = True
.ShowError = True
End With
I've tried using different ways of counting the rows and have been able to define "iRows" the problem is defining the range to only column E from E1 to E"irows."
View 4 Replies
View Related
Aug 1, 2006
I am trying to create a command button on my userform that will allow me to print all the records in the worksheet....in other words it should only print the rows with data......
*my worksheet is called "complaintData" - this is hidden and not active sheet.
*I have columns A to J with information, with heading from A1:J1.
*I want to be able to print all the rows with values in column A. (Not all the columns except "A", will have values for every record....some may be blank for some records...
*I want all the columns to fit in in one page with headings (A1:J1) being first row on every new page.....
*page setup should be landscape...
I have tried a few codes from this forum, but not sure where I have goofed up....am still trying learn VBA.....would appreciate if anyone can fix this code for me....cheers
Private Sub cmdPrint_Click()
Sheets("ComplaintData").Visible = False
Dim ws As Worksheet
Set ws = Worksheets("complaintdata")
View 6 Replies
View Related
Aug 23, 2006
I have tried various syntaxes but nothing is working for me. I am using XL 2000 so it doesn't have the relative address function in the macro! So here is what I have
ActiveCell.Range("A1").Select
ActiveCell.R1C1
Range("R1C1:R1C85").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues
Application. ScreenUpdating = True
Range("A1").Select
I need to be able to select from the originating cell which will always be in "T" Col thru to "CZ" col but will be on different rows.
View 2 Replies
View Related
Feb 6, 2007
I have this problem quite often and always have to think of a way around it, however I think there must be a simple method of doing what I want.
If I want to select row three, I would write:
Rows("3:3").Select
However, if I have a variable which holds my row number how do I rewrite the above code? I've tried serveral varations of:
Rows("CurrentRow:CurrentRow").Select
View 9 Replies
View Related
Aug 8, 2009
On the attached Excel file, I have code that will insert a variable number of rows and copy and paste from and to variable positions. That all works fine when run from a command button, but when I try to run it from the Worksheet_Calculate by entering 1 in J1 or K1 (inrange cell is J1+K1 for testing purposes) the CommandButton1_Click sub runs continously until an error occurs.
View 4 Replies
View Related
Feb 20, 2009
I'm fairly new to macro's and VBA, by searching on the internet i've copied and pasted some code together into a macro.
But it ends in a Run-time error 91...
The macro opens a target .xls file in a selected folder, performs copy - paste actions from masterfile to targetfile.
Than it filters data in the targetfile sheet1 and copy's the results to the various other sheets; saves and closes the targetfile.
The next target file in the folder is opened and the actions are repeated in this second target file.
For the first target file this works smoothly; but for the second one (of a total of around 100) it does not copy the filter results to the other sheets in this workbook.
The error message i get is: "Run-time error 91:Object variable or with block variable not set."
When i hit debug it highlights the line "ActiveSheet.Next.Select" which, at least in the first file, seems ok.
View 9 Replies
View Related
Mar 1, 2009
1. Is there a way to delete all records below a variable row which I select highlighted ?
2. I have a spread sheet which is located in the folder xl start. How can i find this file to delete it>
View 5 Replies
View Related
