Adjusting Code To Use Alternate Cell Ranges
Apr 5, 2009Here'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
ADVERTISEMENT
Adjusting If -Then Statement In Code
Dec 8, 2009I am trying to make some additions to this code. Basically what it does is copy some data from another sheet. Now there are two types of copying here.
1. A ‘call value’ from another sheet is copied to this sheet.
2. The formula from the previous cell in the column is copied. (eg. Formula in C3 is copied to C4, formula in D3 is copied to D4, and so on)
So the macro works fine for all cases EXCEPT the initial entry, which will be in Row3, that’s where the formulae begin. So I need some kind of an If-Then statement.. to say if A3 is empty then do the following, If not then do something else....
Adjusting VBA Code From A Spreadsheet List
Oct 3, 2008below is some code from a spreadsheet I am working on,,,,,, as you can see it adds items to a dropdown list in a combo box. This may require an update at another time,,,,, is it possible for the list to be updated from a list in the spreadsheet, therfore preventing people from accessing the code
View 4 Replies View RelatedAdjusting VB Code To Combine Only Certain Worksheets
Mar 26, 2014I saw the below code in another thread that does almost what I need it to do. The only thing is that I need to only select certain worksheets, not all. Is there an adjustment I can make to this code or is there something I can do differently?
Each worksheet has a table on it as well, is there a code I could use to just combine certain tables?
Sub debit1()
'Combine all worksheets to the Summary sheet
'Created by Trevor G 30 June 2011
Dim ws As Worksheet
[Code]......
Adjusting Macro Code To Be Able To Sort Data?
Oct 17, 2012I have a worksheet that is automatically updated based on actions in other sheets. There are 10 columns of data (A-J) Headings are on row 6 and data starts on row 8. I have created 3 buttons to sort worksheet by client column (B), year-end column (E) and to bring it to original order (by numbers in column A). I have created the following codes that I have assigned to each button however it does not work.
Sub Macroclient()
Columns("A:J").Sort Key1:=Range("B8"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub
[Code]....
Code To Protect And Unprotect Cell Ranges?
Jan 28, 2014following 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]......
Code To Print Ranges Based On A Cell's Valvue
Nov 7, 2009On 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.
HLookup On Alternate Cell?
Mar 5, 2012Im wondering if their is a formula to return the results in a Hlookup but adjusted for the cell 1 cell to the right?
For instance if Row 1 Column one contains "Tom Jones" that the forumula knows to return the value in row 2 column 2 instead of row 2 column 1.
Adjusting Percentage Values On Cell Change
Jan 8, 2009Good afternoon Gentlemen, I have a column of data, with a varying number of percentage values that add up to 100%, separated by "NA", i.e.
33%
33%
33%
NA
25%
25%
25%
25%
NA
100%
NA
20%
20%
20%
20%
20%
NA
Now... when I change one of the values I would like the others to even up, i.e. in the last example if I change a 20% to 50% I would like the others to change to 10%... any ideas?
Non-Continuous Range In Event Code: Show A UserForm When A Cell In 1 Of 31 Named Ranges Is Selected
Nov 7, 2006I 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....................
Adjusting External Cell Reference (Links) In Excel
Aug 7, 2013The goal is to have a data validation drop down list that adjusts the File.xls name portion of the external cell reference (example given below) and return the result of the cell I am searching in the new file after updating my links.
I am trying to set up an external cell refence within my workbook for example:
='[File.xls]Tab'!$C$9
When I have tried setting the equation to pull from the drop down list in cel A1 for example:
="'["&A1&"]Tab'!$C$9"
It only shows the new file path name and not the value in the new sheet--even after updating links.
I am also attempting to do this using the my network places file path instead of the letter drive in my computer.
If Row Contains Non Empty Cell Copy To Alternate Worksheet
Sep 11, 2013When the worksheet is changed;
-For each row between A3 and A5000
-If cell in column L is empty
-Do Nothing and move on to next row
-Otherwise if cell in column L is not empty
-Copy entire row to alternate sheet, and delete row from original sheet.
-After all rows between A3 and A5000 have been checked, sort alternate sheet in ascending order based on the contents of column A.
The issue is that the code keeps skipping some rows that should be copied, possibly due to the 'for each' command not liking how I'm deleting rows (maybe?)
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCell As Range
Dim lRow As Long
lRow = Range("A3:A5000").Rows.Count
[Code] .....
Duplicating Multiple Buttons Adjusting Target Cell Automatically
Jun 6, 2014I tried to search the forum for my unique problem but had no luck. As you will see attached, I have a series of excel buttons I need to duplicate and have target a different set of cells. I am hoping there is a way to avoid manually doing this.
The goal of the document is to push the button when both colours in the row and column interact (research on birds). There are two additional behaviours with an exact same set of buttons but they need to target "Body Rush" and "Food Displacement" tables underneath. I need all the buttons on one page since multiple behaviours happen simultaneously that need to be recorded.
I have something like 100+ modules in VBA I have created, I am hoping there is an easier way to do this so I don't have to create another ~200 modules in order to get the last two behaviours setup.
I am hoping there is either an easier button system or way to make buttons adjust somehow.
I am not the most advanced VBA user
Finding Min Cell Values Excluding Zero In Alternate Columns
Nov 4, 2005I have an array that is 1 row high by 16 columns wide.
Each cell may contain a positive value, or a zero.
I need a formula to find the "Minimum value that is greater than zero" in
columns 1,3,5,7,9,11,13 and 15.
=MIN(A1,C1,E1,G1,I1,K1,M1,O1) will always return the zero value while I
need the minimum value that is greater than zero.
If I use nested IF functions to exclude zeroes I run foul of the max of 7
allowed.
Excel 2003 :: Copying Cells Based On Alternate Cell Value?
Sep 26, 2011I am wondering if it possible to automate the copying of data from particular cells, based on a value in a different cell, into a different format.
So to go from this simplified table:
AB1NameLevel2Arthur2a3Briony3c4Catherine3b5David3a6Edward2a7Felicity3c8George3c
to something like this:
FGHI12a3c3b3a2ArthurBrionyCatherineDavid3EdwardFelicity
4
George
At the moment I do it all manually, and it takes forever. I am sure there must be a simple way of doing it. I am using Excel 2003, but could work in a newer version if required.
Code Vlookup To Different Ranges. #N/A!
Nov 9, 2006I 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), _ .....................
Creating Zip Code Ranges From One List?
Aug 21, 2009I 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
VBA Code For Applying Borders To Ranges
Mar 12, 2009I 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)
Code To Update With Names From Ranges
Apr 18, 2007I 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 ...
VB To Expand Alphanumeric Code Ranges Into New Column
Jan 8, 2013I 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.
Run SAME Code For Different Ranges Using Before Double Click Event
Apr 4, 2013code 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.
VBA Code For Printing Variable Print Ranges
May 5, 2009I 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
Create Dynamic Named Ranges With Code
Oct 13, 2007I 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.
How To Use Named Ranges In VBA Code Using Columns And Variation On Range
Mar 17, 2014I 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] .....
Code Required To Ensure Minimum Of 6 Rows Between Ranges
Oct 26, 2008I 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?
Excel 2003 :: VB Code To Delete ALL Named Ranges
Mar 22, 2012I 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.
Adapting Code To Merge Data From Multiple Ranges
Mar 15, 2009I'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.
Adjusting A Formula
Apr 18, 2008I am trying to figure out a formula for the boss, but I just cannot wrap my mind around what I need to do. I’m sending out an S.O.S.! If you can figure this out, please respond.
=MIN(b4*12-25%,b4*12-500) + MIN(c4*12-25%,c4*12-500) fills in the cell with the correct answer, but not quite the answer that I need. Let me illustrate:
The first part of the formula is to take b4 times 12 minus 25% --if this amount is less than or equal to 500 then use the amount. If it is more than 500 then use 500.
(b4*12-25%)
234.25 * 12 = 2811 – 25% = 2811
2811 - 25% = 702.75 500 Since this amount is more than 500, 500 should be used instead. ~~this is actually the amount that I want to show in the cell, the amount saved~~
2811 - 500 = 2311 ~~this is the answer that is showing up in the cell, which is not quite what I need. ~~
I want to find the answer to this question: b4 times 12 equals ?, subtract 25% or 500 from that, insert this answer into cell.
One of my biggest problems with this is that I can’t even figure out how to say what it is that I need. The formula is doing exactly what I'm telling it to do and I can't even figure out what it is that isn't working. This formula is confusing my brain…!
Macro Code To Average Multiple List Of Varying Column Ranges
Apr 4, 2009I 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
Adjusting Row Height To Fit Text
Dec 10, 2012How Auto Resize (height) cell to fit text? (macros, formulas, setting)? One can make the height of the cell is reset according to the amount of text?
ej..jpg