Im using the following HLOOKUP formula : =HLOOKUP($B$14,$DA$15:$EH$380,2,FALSE) and I need to drag it down 365 times, but the row index num, ie ‘2’ does not increment with the drag. Is this correct – or is there a way of making the 2 increment?
View 3 Repliesi'm trying to use HLookup to find an adjusted midterm grade that's given. but i have some conditions:
If student missed exam and has a zero – keep zero.
If student has a grade of 1-119 points, increase their grade 40 points.
If student has a grade of 120-125 points, increase their grade 35 points.
If student has a grade of 126-131 points, increase their grade 31 points.
If student has a grade of 132-139 points, increase their grade 27 points.
with these conditions, if my midterms grade is 120, how would i calculate it using HLookup? i worked on it but i keep getting the #NA! error.
=H4+HLOOKUP(H4,B24:D25,2).
I am trying to import a BASE ESTIMATE table into EXCEL.
I have problems with most of the formulas, especially this one:
=VLOOKUP($E$2,$B$24:$P$604,HLOOKUP($E$3,$D$22:$L$604,1)+2)*HLOOKUP(HLOOKUP($E$3,$D$22:$L$604,1),$D$2 2:$L$23,2)
and this one
=ROUND((IF(AND(OR(E7>E5,E7>E6),E3<40000),E7,IF(AND(E6<E5,E3<40000),E6,E5)))*E8*1.055*1.06,2)+10
I am not sure if EXCEL allows a HLOOKUP within an HLOOKUP. If not, how can I get around this?
Im trying to create a catalogue for our complaints that come in each day at work. Ive created a basic layout I like, it uses 3 cells for a week day and 1 cell for a weekend day. The date is listed on the side of each day. I would now like to be able to quickly use this format for the rest of the month/year but need a way to do this easily as I cant do each day separately in the future. Ive tried selecting cells and then dragging down but all it does is repeat the previous 7 days and the same dates instead of incrementing the date.
Ive attached an image ...
If I'm in a worksheet and I spot a row I'd like to copy into my second worksheet, how do I arrange to have it increment the rows?
EX: searching through a worksheet, I see a suspicious entry. I want to copy it into another worksheet. I use the macro. I go back to the original worksheet and see another suspicious entry that I'd like to copy. I'd need a macro that would copy the active row from sheet one, and increment the rows on sheet two before pasting.
I create inventory cards and each needs a unique 4 character sequence number. Currently I have it set up with 4 cards per sheet. If I need to print more than 4 cards, the worksheet will print one sheet of 4, reset itself and increment the sequence number by 4 and continue this cycle until I've printed the number I need.
Currently I have it only using numbers. I start at 1000 and it increments, by 4, up to 9995. Once it reaches 9995 or higher, the increment resets to 1000 and starts over. The problem I am facing is I am now starting to get several cards that have the same "unique" sequence number. What I would like to do is change it from numerical sequencing to alphanumeric sequencing.
This is the code I currently use for the incrementing....
In one of my rows I am a referencing another sheet:
The formula is =Sheet2!e4. In the row below it I have =Sheet2!E8. Now is there a way to drag these formulas down so they keep incrementing by 4?
I've got above basic VBA knowledge and learning very quickly (largely due to great forums like this) I'm now creating a userform for the first time. I'm trying to create a New Supplier form and one of the fields is going to be for Supplier ID where it takes the first 6 letters of the Supplier name (entered by the user) & tags on '001' at the end. I've managed to do this but I would like some code to search existing supplier ID's ( Column A on separate sheet) and check whether the ID created already exists and if it does add on '002' instead of '001'.
SO for example..
If user creates a supplier called Microsoft the supplier ID will be Micros001 - this I can do, but if Micros001 already exists then it will generate an ID
Micros002, Micros003 and so on...
I have a column that I want to insert number 1 through ? incrementing by one. Can I do this simply without having to type in each one manually?
View 4 Replies View RelatedI am having trouble with the following situation:
on the sheet PANEL in cell A2 I want to reference the information on sheet 480 in cell B5.
Then on sheet PANEL in cell A3 I want to reference the information on sheet 480 in cell B54.
'PANEL'A2 needs to equal '480'B5
'PANEL'A3 needs to equal '480'B54
And so on...
I need the reference to skip 49 cells each time so that I can just copy the formula on down the column.
I'm putting together a document where I'm trying to automatically create a reference number for each entry. The reference number needs to consist of a site ID (found in column A), an underscore, and then an incrementing number dependent on how many entries the reference number appears.
For example, in column A, the first time the site ID: 12345 appears, I'd like the reference number 12345_01 to be created. The second time it appears, the reference number would be 12345_02. And so on.
If possible, as illustrated above, the first nine references need to have a 0 before the number - i.e. 01, 02, 03, etc - when it hits the tenth instance the reference becomes 10, 11, 12, etc.
I've tried to solve this with COUNTIF, but cannot get it down.
Is there a way to count how many times a value is changed?
Cell A1 can be either “YES” or “NO”. Its' value is controlled by validation…
When Cell A1 is changed from “YES” to “NO” I want to increment the value in B1 by one.
When Cell A1 is changed from “NO” to “YES” I want to leave B1 as it is.
I have created below vba code but don't understand how to stop the auto incrementing.
[Code] ......
It end ups giving me see below.
Week 5+6
Week 5+7
Week 5+8
Week 5+9
Week 5+10
Week 5+11
And what I want is that it shows in all the lines “week 5+6”
Strange enough when I only enter a number like 5 it doesn’t auto increment.
And if I add a dot behind the 6 then either.
But I just want it AutoFill that what I have inserted in the box!
I have a piece of code that runs through multiple tables (they vary in length) and replacing values. All in the same worksheet
My code looks like this:
[Code] .....
How I get c to move to the next table and count the rows in that. In my code c stays constant, and I need it to change and match the new row count.
I have a spreadsheet which I need to remove all the blanks from, but instead of shifting cells up (I figured out how to do that in a macro), I want them to shift left.
I understand the basics of for...next loops etc, but I can't seem to understand how to make the selection in a range string variable. Not sure if this is a good explanation!
The code I have so far is....
I am running a macro where I pass it starting column and it processes the next 10 columns. How can I pass it "J" and have it increment K,L,M,N,O,P,...?
View 3 Replies View RelatedI need to complete what would seem a fairly simple incrementing task but I'm not getting the results that I'm looking for. I don't know much about macros so would prefer not having to use one but I'm willing to try if it's not too complicated.
I'm using Excel 2010 on a PC. For several projects, I need to increment cells that contain text and numbers. The column contains data in a cell (A1), followed by a blank cell (A2), then cell A3 contains new data followed by a blank in A4, etc. What I need to do is 1) copy the exact data in A1 to A2 and then 2) increment the number used in A1/A2 by 1 in A3, copy that into A4, and the pattern repeats down the column.
Here is an example of what I have in column A:
Test_01
[blank cell]
Test_02
[blank cell]
Test_03
[blank cell]
etc.
This is what I want as a result.
Test_01
Test_01
Test_02
Test_02
Test_03
Test_03
etc.
Do I need to save the data in the column as text, general, or numbers? Is there some function that would make incrementing the data possible as I've outlined above?
Incrementing line numbers is a simple task.
If line Identification starts with
AA
AB
AC, why doesn't the same rules apply?
Highlight the cells then click and drag the corner down.
I am currently working on a button which does the following:
User wants to create a new sheet
Clicks button
Input box prompts user to enter sheet name
User enters the sheet name
The sheet is created and copies over formats and values from another sheet
I then want the user to also enter a date.
The date must then be places (always in "C2") then each second column after that the date entered + one day.
E.g. C2 = 01/08/2008 then C4 will have 02/08/2008
I want this entered for the whole month.
The code is below:
Sub copyformula()
Dim vsheet As String
Dim vdate As Date
vsheet = InputBox("Enter a sheet name:")
Here is what I have at present:
At the beginning of a loop, my cursor is positioned on a row. I want to test two cells on that row.
Depending upon the result of the two cells, I want to make the row a certain color.
After that, I then want to move down to the next row, test the same two cells in THAT row, color accordingly and loop again until I reach the bottom of the spreadsheet.
My problem is this:
If I can use a loop that lists various conditions, along with the corresponding statements that color the row accordingly, how can I when finished increment the cell references so that the conditions change to refer to the cells the next row down?
If a loop is not possible, any suggestions you have for solving this problem would be appreciated. I have about 200 rows so am trying to condense my code; this may be hampering my ability to figure out the solution.
I have a sheet with all of my data sorted into columns. I am writing a macro to select and copy the value from column A if there is any value in column B on the same row. The output would be sent across consecutive columns.
As an example;if B3 is not blank, copy A3 to I5
if B4 is blank, do nothing
if B5 is not blank, copy A5 to J5
if B6 is not blank, copy A6 to K5
and so on.
I cannot figure out how to increase the output column identifier. Everything else I can get
I have the following function that looks at a range of data and deletes duplicate entries in each column. I want to change the range that is examined programmatically, rather than having to retype it for each range in this huge sheet! I have subtotaled and counted the rows in each range, so I know how many there are, but I'm stumped as to how to increment teh range correctly. I'm attaching a small sample of my data. The full sheet is 30,000+ rows and 94 columns.
Sub DelDups2()
Dim rngSrc As Range
Dim NumRows As Integer
Dim NumCols As Integer
Dim ThisRow As Integer
Dim ThatRow As Integer
Dim ThisCol As Integer
Dim J As Integer, K As Integer, x As Integer
Set rngSrc = ActiveSheet.Range("c68", "cr70")
NumCols = 94
NumRows = rngSrc.Rows.Count
ThisRow = rngSrc.Row
ThatRow = ThisRow + NumRows - 1
ThisCol = rngSrc.Column
For x = 1 To NumCols
For J = ThisRow To (ThatRow - 1)
If Cells(J, ThisCol) > "" Then................
I have a formula that i'd like to "click and drag" down but while i do i want it to increment through columns
a
b
c
[Code]....
in cell A1 i'd have the formula
VB: =max(c1:c5)
and it will spit out 15, that's great but when i drag the formula down i want cell A2 to give the value 20
i'd like
VB: =max(c1:c5)
to somehow turn into an equivalent
VB: =max(g1:g5)
by only dragging down, not to the side
I have a spreadsheet with a summary tab and 30 data tabs. The data tabs are named page-1 to page-30. In the summary page I have the following formula in cell C39: 'page-1'!C20
I want to be able to drag horizontally across 30 cells and have it increment to 'page-2'!C20, 'page-3'!C20 etc.,
and also drag it vertically and have it increment to 'page-1'!C21, 'page-2'!C22 etc.
I am working on a simple parts numbering system.
I am using a userform to allow the users to pick from two combobox to generate the first 3 digits on the part number (which are inserted into a textbox automatically), the final 4 digits are auto generated using the following code:
[Code].....
This is called when a button is pressed, this then checks the already existing parts sheet to see if the number exists, if it does then it will ask for another number, incremented with the previous code. This will continue until a unique number is found.
This is the code that does this:
[Code] .....
My issue is that if I reset the form to create a new part number which is in another catagory, I need the auto increment to reset to 0, so the new part number can be checked. At the moment it carries on incrementing from the point the form is reset. The same happens if the user decides to change a catogory (maybe for the first part in the catagory, should be ***-0001), if the last part was ***-0055, then it will assign the new part 0056, not 0001.
So how can I reset the auto increment to back to zero on for example a listbox / combobox change?
I've got a sheet with 3 columns A, B and C headed: "Surname", "Forename", "Membership No" with then a variable number of rows containing the data. There are no blank cells (the macro already checks for this).
In column D I have "Group Name" which I need to populate with GrpA for the first 250 members, GrpB for the next 250 and so on down until it stops with the first blank cell in column C. At the moment I'm happy for it to cope with up to 2000 members, i.e. 8 groups, though may need to extend it later (no danger of getting past GrpX though!).
What I would like to have done is to look for each cell in column M with only the string of "note" in it, then look one cell below and to search the text using boolean operators and if certain words are found, increment a series of counters.
For example, using cells M16 and M17 as references, if "please" AND "relationship" AND "customer" are found within the text, create a counter and increment it by 1. The program should loop through all of the cells in column M and determine how many instances of that particular set of words is found. If there are 20 instances in the column then somewhere on the sheet, counter 1 should indicate 20.
There are a couple other set of words I would like to search as well, with each new set creating a new counter and indicating the total instances found of that particular set of words. For example, in cells M40 and M41 I would like to search for "failing" AND "name" AND ("first" OR "last"). Thus, if there were 50 of these instances a second counter somewhere on the sheet would indicate the number 50 when the program finishes.
I was using the find and replace but I don't think I can use boolean operators with it, and I'd also like to learn how to do this programmatically.
I'm attempting to write a simple piece of code to correct a formatting error in a spreadsheet. To fix the error I need to move a row of data up one row and to the right 2 cells. Then Delete the empty row and repeat until the end of the section that is messed up. I currently have the user selecting the range to copy, the cell to paste to and the row to stop at. The copy and paste aspect is working as intended however the macro isn't incrementing the rows correctly. This plus the deleting of rows I believe is causing the macro to crash. Is it possible to increment the rows of a range? When I try datarange.col = datarange.col + 1 I get an error.
View 5 Replies View RelatedI use spinbuttons to increment 3 data cells (C7:C9). These cells are used as entry data for some calculations and also trigger some macros via change event. Every time I use them the workbook crashes, this is only since I modified one of the instantiated macros that sorts a table on another sheet. See below the spinbutton code with the call of the macro and further the code of the sorting macro:
Private Sub SpinButton1_SpinDown()
Range("C7").Value = Range("C7").Value - 0.05
End Sub
Private Sub SpinButton1_SpinUp()
Range("C7").Value = Range("C7").Value + 0.05
End Sub
Private Sub SpinButton2_SpinDown()
Range("C8").Value = Range("C8").Value - 0.05
End Sub......................................................
I currently have a project i am working on based on a invoice system and in my case it is to do with Van rentals. what i am looking to do is create a macro so that when the user clicks the order van button, it brings them to the order page and automatically creates an invoice number which is automatically added to the invoice database along with the customers details and the date of the invoice in a different sheet.
i have been browsing various forums and tutorial and have come across an a piece of code that is incrementing numbers each time the button is clicked. I can add the first set of details to the invoice database however i am unable to create a new row which copies the formulas and just change the invoice number as it is incremented - it just copies the same number
Now i am a complete novice in using VB and i have knowledge to an extent using excel. Is it possible to do that?
Sub test()
'
' test Macro
Sheets("Invoice List").Visible = True
Sheets("Customer Menu").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Customer Van Order Form").Visible = True
ActiveWindow.SmallScroll Down:=-12
Sheets("Invoice List").Select
ActiveCell.FormulaR1C1 = "='Customer Van Order Form'!R[7]C[6]:R[7]C[7]"
ActiveCell.FormulaR1C1 = "='Customer Van Order Form'!R[7]C[6]"
Range("B2").Select.............