VB To Expand Alphanumeric Code Ranges Into New Column
Jan 8, 2013
I have a column containing ranges of apha-numeric codes that I wish to expand into an adjacent column as the following example:
AL 0-4
AL 0
E 4
AL 1
H 16-23
AL 2
[Code] .....
I would like the VB to distinguish and expand the different range permutations from the first column, leaving single codes intact and separating comma separated codes as shown. If it makes it easier, I manually split out the comma separated codes onto separate rows.
View 4 Replies
ADVERTISEMENT
Dec 10, 2006
I have two ranges. I need to add a row at the bottom of the first range and autofill formulas in the second range using the new added data
View 2 Replies
View Related
Jun 23, 2009
I need to automatically populate a column with an alphanumeric MFR0001 to MFR9999 and am currently using the formula below to add 1 to the start point (A1)...
A1=MFR0001
Formula in A2 =LEFT(C1,3)&(RIGHT(C1,4)+1)
This works perfectly if I start at MFR1000, but breaks and drops the 000 if I use it on the number I need to start with (MFR0001).
View 3 Replies
View Related
Feb 10, 2014
Currently, Columns EFG are grouped.
I have a userform with checkboxes. With the click of check box, i would like for it to do the following.
If checked, it will expand the group. If unchecked, it needs to stay collapsed.
Here is an example of code i currently have and is not working.
[Code] ......
View 2 Replies
View Related
May 13, 2011
I have several groupings of rows in worksheet. I need to have each separate grouping be able to automatically expand or collapse based on a value in each group.
ie-
Grouping Rows 11-15
Grouping Rows 21-25
Grouping Rows 31-36
If cell values in A10, A20, or A30 = 1, then the grouping below it would need to be collapsed, and if not, then expanded.
View 3 Replies
View Related
Dec 2, 2013
I am having a small problem with microsoft excel 2007.the problem is that: my excel 2007 cannot display tooltip of the column width when I keep and drag left mouse
Show tooltip of the column width as attached file.
I also tried many ways to find settings of advance menu. But cannot.
Pic.jpg
View 2 Replies
View Related
Mar 30, 2013
I have 2 drop downs that when changed, auto refresh all pivot tables. My problem is getting the columns to auto-adjust based on the refresh or change of the data in the pivot table. Listed below the code I have thus far for the auto-refresh on pivot tables. How to auto expand all columns simultaneously.
The pivot tables are based off of tables on a different sheet(TOS Tables). So the code listed below is in the table sheet, not the pivot table sheet (TOS Customer Level). So followup question will be, which sheet to put the auto-expand columns code?
VB:
Private Sub Worksheet_Calculate()
'If data on this worksheet changes, refresh the pivot table
Sheets("TOS Customer Level").PivotTables("PivotTable2").RefreshTable
Sheets("TOS Customer Level").PivotTables("PivotTable5").RefreshTable
End Sub
View 3 Replies
View Related
Apr 4, 2009
I have a spreadsheet that has two different data sources (i.e., A and B). The amounts of these data sources (i.e., the number of columns) varies from sheet to sheet. I have to calculate the averages of these data sources independently and together. Because this data is spread across twenty or more spreadsheets, these calculations can be time-consuming. I want to do the following:
1. Locate the last occurrence of the first data source "A" in row and then insert a column after that cell.
2. In that cell, I want to get the average of each data source for each row of data (i.e., there are always 19 rows of data).
I want to do the same two steps for the second data source "B". Then, I want to insert a column after the "B" average and this column will be used to get an average of each row of data from A and B together. Please keep in mind that the number of data sources for A and B varies from sheet to sheet.
So far, I am working on code to try to " find" the text in a range (i.e., find the last instance of "A"), but I cannot figure out how to get it to get it to stop at the last occurrence and then insert a column. I have some ideas about how to calculate the average, but any of yours are much appreciated.
Also, the row with the type of data (i.e., A or B) is named because this function is part of a larger macro. Therefore, it is relatively easy to get to it.
I have attached a version of the file that displays how I want it to look.
The code I have thus far is:
Cells.Find(What:=A, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
View 9 Replies
View Related
Apr 1, 2014
I would like to add the alpha numeric code "UK" at the beginning of the code listed in the excel file.
How to add this syntax with the listed code in excel file?
View 2 Replies
View Related
Apr 1, 2014
How to make the below selected range, which represents column Q, also include column A?
Range(ActiveCell, ActiveCell.End(xlDown)).Select
View 4 Replies
View Related
Mar 24, 2014
I'm looking to populate a series of sequential codes based on a code prefix.
Sheet 1: Master Code list
Column A = Lookup Value
Column B = Code Prefix
Sheet 2 - Results page
Column A - list of lookup values - sorted in value order - The number of occurances varies for each value
Column B - Results list
I want to populate Column B in the second sheet with a sequential code list for each different value in column A. So for the first value it would have the prefix then proceed numerically (Prefix0001, Prefix0002, Prefix0003...) then for the next value its prefix in sequence (2ndPrefix0001, 2ndPrefix0002, 2ndPrefix003...), etc
I'm wondering if there is a way to do this all in one step or else if there is a way way to number each occurance of the lookup value in the second page, in which case the code can be concatenated easily by a lookup of the code.
I suppose on the back of this there is another query - is there a way to concatenate a number where the format is 000# where the concatenated value will retain the leading zeros? I've been trying and it is stripping them out.
View 2 Replies
View Related
Oct 25, 2007
I need help creating a macro that will search through my excel spreadsheet and for every instance where column A isn't empty it should cut a range of columns from that row and paste them in a different range of columns in the row before it. It should then delete the row that it cut the columns from and keep searching until it has done this for the whole worksheet. I can modify which range of columns are needed, but it has been so long since I've worked with excel macros that I haven't been able to do it.
View 5 Replies
View Related
May 9, 2014
Output should be:
.Column A
AB-CD/EF1-AB
AB-CD/EF1-BC
AB-CD/EF1-AB(100%)
100%
0%
AB-CD/EF1-AB(30%)
30%
0%
AB-CD/EF1-AB(4%), AB-CD/EF1-BC(2.4%)
4%
2.4%
Likewise, I have many combinations. And it is not necessary that only "AB-CD/EF1-AB" combination will come first in a row. In input,Column A values are given. Other columns are empty.
View 9 Replies
View Related
Aug 2, 2012
I'm trying to sort by a column with one to four digit numbers, any of which may be followed by a single letter, i.e. 1,2,3 4A,5, 10,11A,75,101A,600,705,1010B,1011A.
I'd like them to sort in the above order. I am only able to have excel 2003 sort all of the numeric cells first, followed by the cells with the alpha character.
View 4 Replies
View Related
Nov 9, 2006
I am trying to use vlookup in vba but i receive an error '1004' "Unable to get the VLookup property of the Worksheetfunction Class". I need a way around this. I have ID numbers in the first column and I am trying to input the name of the table it corresponds to in column "O". I have 4 different tables. I am using For Each cell in Selection to go through every cell by using If statement along with VLookup. As soon as the function is forced to go to the next IF selection, it gives me the error.
Range("O3:O" & myCount + 2).Select
For Each cell In Selection
If IsEmpty(cell) Then
If cell.Offset(0, -14) = Application.WorksheetFunction.VLookup(cell.Offset(0, -14), _
Workbooks("Warehouse Inventory"). Sheets("TWGReports").Range("$a$2:$z$1000"), 1, False) Then
cell.Value = "TWG Facility"
If cell.Offset(0, -14) = Application.WorksheetFunction.VLookup(cell.Offset(0, -14), _ .....................
View 3 Replies
View Related
Mar 7, 2008
I have two different Excel reports and the data needs to be copied from each column on one spreadsheet and pasted to the bottom of the equivalent column on the other spreadsheet.
So, is it possible to get a macro that will copy the data from one column then paste it to the next free cell in the column on the other spreadsheet.
If I could get some code to do one column then this should be enough to get me started and I could apply this to the other columns I need to copy.
View 8 Replies
View Related
Aug 21, 2009
I have a long list of zip codes (this is just a small portion of the list) that I need to make into ranges. Is there a formula I can use?
From this list:
90080
90081
90082
90083
90084
[Code] ......
To this:
To From
9008090084
9008690089
9009190091
9009390099
9010190103
9017490174
9018590185
9018990189
9020190202
9020990213
9022090220
View 3 Replies
View Related
Mar 12, 2009
I need to apply borders to a certain range. Is there anything I can do to shed some fat (code) off the macro below?
Range("A8:AD100").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
View 6 Replies
View Related
Apr 18, 2007
I need my SQL server query to update with names from a range. e.g. pinnog as shown below is a name defined range called Name1 and smithb will be Name 2.
Can someone with me with this code as I am not sur ehow to lookup ranges in VBA.
My code is below ...
View 6 Replies
View Related
Apr 4, 2013
code to have check marks appear in multiple non-adjacent columns in a worksheet by double clicking.
The code I am using works for a single column : (The named range "Checkboxes1" is D4:D100)
VB:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("Checkboxes1")) Is Nothing Then Exit Sub
[Code]......
However I also need the code to work on two other named ranges "Checkboxes2" (E4:E100) and "Checkboxes3" is G4:G100.
View 5 Replies
View Related
Apr 5, 2009
Here's a nice easy one for anybody whose used excel for more thaan a week unlike me, I'm using the following code to access a popup calendar:
View 2 Replies
View Related
Jan 28, 2014
following code so as to have certain cell ranges protected and unprotected USING COMMANDBUTTON/PASSWORD
Code:
Private Sub CommandButton1_Click()
Dim Password As String
JamesPassword = "MyPassword" '
[Code]......
View 8 Replies
View Related
May 5, 2009
I am trying to write VBA code that will print a print range that is presented in cell F3 on a "Reports" worksheet. The content of F3 will change depending on how many reports the user selects to print. For example, he could select one, two, three reports etc - up to twelve. The cell ranges of each report are named (e.g. Report1, Report2 etc) so that if the user selects to print Reports 1 and 2, the contents of cell F3 are "Report1,Report2". If I replace WhatToPrint with "Report1,Report2" the print macro works.
Sub Macro2()
Dim WhatToPrint As String
WhatToPrint = Sheets("Reports").Cells(3, 6).Value
'sets the variable to equal the contents of cell D3 which contains the formula
'summarising the print ranges I want to print
Sheets("Reports").Cells(3, 6).Select
ActiveCell.FormulaR1C1 = WhatToPrint
' pastes the variable in cell F3 - just to check that it looks like I want it to
Sheets("Reports").PageSetup.PrintArea = WhatToPrint
'uses the variable to set print area - this is where it fails!
'if you replace the variable with the contents of cell F3 the macro will work
ActiveWindow.SelectedSheets.PrintPreview
End Sub
View 3 Replies
View Related
Oct 13, 2007
I am employing code to label dynamic ranges that takes the form of,
ActiveWorkbook.Names.Add Name:="dms", RefersToR1C1:="=OFFSET(DMS!R10C5,0,0, COUNTA(DMS!C5),COUNTA(DMS!R10))"
'dms_j
ActiveWorkbook.Names.Add Name:="dms_j", RefersToR1C1:="=OFFSET(DMS!R11C10,0,0,MATCH("" * "",DMS!C10,-1),1)"
'dms_p
ActiveWorkbook.Names.Add Name:="dms_p", RefersToR1C1:="=OFFSET(DMS!R11C16,0,0,MATCH("" * "",DMS!C16,-1),1)"
'dms_r
ActiveWorkbook.Names.Add Name:="dms_r", RefersToR1C1:="=OFFSET(DMS!R11C18,0,0,MATCH("" * "",DMS!C18,-1),1)"
'dms_t
ActiveWorkbook.Names.Add Name:="dms_t", RefersToR1C1:="=OFFSET(DMS!R11C20,0,0,MATCH("" * "",DMS!C20,-1),1)"
The dynamic ranges are getting entered but the problem is that the range "dms" overshoots by six cells into blank cells at the bottom of the table, and the rest of the ranges overshoot by ten cells into blank cell area. I have deleted, cleared and destroyed everything around the table and re-sized the active area to no avail.
View 9 Replies
View Related
Mar 17, 2014
I am trying to clean up my code by using named ranges so that it will still work if/when others add columns or rows to the spreadsheet. These should be pretty easy solutions for most of you but I can't seem to find any answers online.
Here are 2 examples of the code I am trying to update:
I was thinking that replacing "A" with "namedRange" would work but I suppose that's not how the Columns application works. How can I modify the code below to work with a named range instead of the fixed column "A"?
[Code].....
For the following I would have thought that replacing "AU" with "namedRange" would do the trick as that's how things have worked for me in the past using the Range application. Unfortunately I get an error when I make the change.
[Code] .....
View 2 Replies
View Related
Oct 26, 2008
I use excel 2007 and have a s/s running to 350,000+ rows.
Data is in ranges of 2 to 30 rows with blank row between each range.
Column F numbers the rows in a range (if row 120,000 begins new range then F1200,000 = 1, F120001 = 2, etc).
If there are 2 rows of data in a range then, including blank row, there are 3 rows between this and next range.
What I want to achieve is a minimum of 6 rows between each range.
Does anyone know code that can can achieve this end?
View 3 Replies
View Related
Nov 7, 2009
On the attached sheet I would like to set the print range and then print based on 1 cell's valve. F9 is the value I would like to use:
If F9=1 I need it to print A1:M43
If F9=2 I need it to print A1:M87
If F9=3 I need it to print A1:M131 and so on.
View 3 Replies
View Related
Mar 22, 2012
I have a spreadsheet, but it came from another file using the detach sheet method. It has therefore taken with it, all the named ranges that are now superfluous.
It seems a bit long-winded to delete these named ranges one by one manually. Would there be an easy to understand script that would delete them all, so I can start with a "clean sheet".
I'm using Excel 2003.
View 5 Replies
View Related
Mar 15, 2009
I'm in the process of combining a lot of worksheets into one and found the code on the following site:
[url]
which almost does what I need. I understand how it works (i think!) but as a vba novice I can't work out how to adapt the code to copy more than one range from the source workbooks.
What I want to be able to do is copy say the following cells from each workbook:
A1
D1
d4:D7
I think what I need to do is call the getdata macro multiple times with different ranges, but I can't quite work out how to do that without simply creating multiple copies of the master file.
View 9 Replies
View Related
Nov 7, 2006
I am using the following Selection_Change Event to show a UserForm when a cell in 1 of 31 named ranges is selected.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Dim i As Long
For i = 1 To 31
If Not Intersect(Target, Range("StatPost" & i)) Is Nothing And Target.Value = "" Then
If Target.Offset(0, -8).Value = "" Or Target.Offset(0, -7).Value = "" Or Target.Offset(0, -6).Value = "" Or Target.Offset(0, -5).Value = "" Or Target.Offset(0, -3).Value = "" Or Target.Offset(0, -2).Value = "" Or Target.Offset(0, -1).Value = "" Then....................
View 3 Replies
View Related
