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.
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.
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")
I download some data from a commercial real estate site about properties and their owners and process it in Excel. Out of 1,000 records, maybe 20 or so will have the data end up in the wrong fields. This is an artifact of the data source the commercial site uses.
Anyway, what I need to do is to get the data back in the right fields. So, I sort the data to pull together at the top of the sheet all the records with data where it's not supposed to be. So far, so good.
Now from one data download to another the number of records which end up in the sort will be different. And, here's the problem.
I try to record a macro mimicking my selection of the range of the data that needs to be moved. Fine, no problem. And, on the same dataset it works like a charm. But as soon as I put a different dataset into the spreadsheet with a different number of records that need to be corrected the macro fails.
Apparently, this is because the macro has been defined with a certain range of cells selected in the first data set and this same range is used for subsequent datasets with different numbers of errant records.
Basically, what I'm trying to record in a macro is the Shift/Control End and Shift/control arrow commands. But they don't record as such.
Based on if the value in col A contains the characters "TT" I want to select the range starting with this cell and ending at the end at the end of the row I'm using (.End(xlRight) and then merge these cells, change colors etc. And then looping this through a 'range' so that it only occurs where the values occur. I can amend various cells based on this idea, but am unable to identify the range and then merge the cells.
I want to select the variable range somewhere in the middle of the sheet from where the 2nd instance of cell named "real cost" is, down to the next blank row (select the area without the blank row), so that I could copy it to another sheet.....
I RECORDED THIS MACRO BUT I WILL LIKE TO MAKE TO AUTO SELECT THE ACTIVE CELL RANGE IS VARIABLE ON MY REPORTS MY CODE
Sub FORMAT_AS_A_TABLE() ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$L$1900"), , xlYes).Name = _ "Table1" Range("Table1[#All]").Select ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleDark5" End Sub
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.
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
Private Sub CommandButton1_Click() Worksheets("Sheet1").Activate Range("A1").Select Selection.End(xlDown).Select ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate ActiveCell.PasteSpecial End Sub it errors to: SELECT METHOD OR RANGE CLASS FAILED
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:...............
Say you define a public range variable called Inputworksheet and you set it to refer to the worksheet called Inputworksheet. You have a separate string variable with the value Inputworksheet. How do you get this string variable value to call/control the range variable Inputworksheet?
I am getting an excel worksheet value from a lookup function that corresponds to the name of a VBA range variable. Once I have this worksheet value, I would like to use the range variable that has the same name as the worksheet value.
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)
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 ".
I have a command button on sheet MASTER. When the workbook is Activated I want it to check and see if in sheet COSTM, cell B3 there are the words "Project Number", if so then show command button (ClearPrevious), if not, don't show. Also, when the If statement is finished, then the workbook needs to end up showing the sheet MASTER. I have tried various codes and none work, or they are on perpetual loops. I know this has got to be simple, but cannot find an example to take from to solve the issue. Would appreciate any help offered. Below is code I have right now.
Private Sub Worksheet_Activate() If Sheets("COSTM").Select Range("B3").Select = "Project Name:" Then Me.ClearPrevious.Visible = True Else Me.ClearPrevious.Visible = False End If Sheets("MASTER").Select End Sub
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."
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