i need to be able to alter the tab which is specified in a VLOOKUP command depending upon which value is given in a drop down box.
ie if the drop down box reads 2 the VLOOKUP should search for an item reference given on tab 2 etc.
I'm trying to figure out how use the Vlookup command. ( Sheet to Sheet, same workbook) Can someone explain the process one goes through to set this up? I have a grip on most of it but seem to be missing some small step.
What I have is this: On one sheet I have 50+ rows of information. Each row has 3 columns, the column number, text which describes a type of construction, and then the last column has the associated R-value. I want the user to be able to use a pull down in a cell on another sheet which describes the contruction and then return the R-value associated with that constrution.
I have two Columns A AND B.AND 5265 ROWS
In column a I have Trx of customers and column B I have customers name. One customers have make many trx in a month. So I want a total that One customer made how many trx in a month. File is attached.
I need to know if I can use vlookup and the if command in the same formula.
I am trying to attach a workbook but it keeps taking me to log in but I am already logged in so why cant I attach it?
Starting at cell H4 and down, I have data pasted to whatever last row, this is a helper sheet/vba thing that helps to create the Case Statement for SQL queries, we have many of them, broken into Pages. So this works great, grabs the data and puts 'data','data2'
The only thing is that in the last data cell in the loop, it leaves off an extra comma, as in:
'306','307','308','309','310','311','312',
I need help in removing that extra comma if there is nothing left to process in the loop so it would look like this:
'306','307','308','309','310','311','312'
Sub Test_Range_Loop()
Dim x As Variant
Dim rng As Range, cel As Range
Dim lr As Long
lr = ActiveSheet.Range("H" & ActiveSheet.Rows.Count).End(xlUp).Row ' find the last row from the bottom-up using H
With ActiveSheet
Set rng = .Range("H4:H" & lr)
For Each cel In rng
x = x & "'" & cel.Value & "',"
Next
.Range("I1").Value = x
End With
End Sub
I have a userform called "DBQuery" and textbox in that called "Query".
What i need is a piece of code that will search the text in the box (after update of course) and Capitalise certain words (SQL ones like "SELECT, FROM, WHERE, AND, IN, LIKE, IS, NOT") and chenge the font colour blue?
I'm currently trying to create a rota timesheet which automatically takes shift patterns and deducts for unpaid breaks.
Basically if a shift is less than 4.5 hours long then no break is deducted
If the shift length is 4.5 - 6.5 hours then 15 mins are deducted
If the shift is longer than 6.5 hours then 30 mins are deducted.
The Macro Sub Match() works great, it is blazing fast.
It matches Col B on Sheet(1) to col B on Sheet(2) and returns the match from sheet(1) to sheet(2) by inserting a col D in sheet(2) and returning the match found to the row at which it was found in col B.
But I need to return not just the matching name from col B but the cells on the same matching row from col C, D, E, F, G, H ,I from sheet(1)
I do not understand to code well enough to do this
I am trying to replace this which is very slow
[Code] .....
I've a file that uses user respone to navigate through a number of charts like a slide show (not my work - downloaded from PHD I think). To move from one 'slide' to the next users are required to select "Ok" on a OK/Cancel Msgbox. By default the MsgBox pops up in the middle of the screen. I'd like to control the position of the MsgBox programmatically.
View 4 Replies View RelatedI need to changing the time data in my Time of Turn. I would like to subtract one hour from every data entry in the column Time of Turn, ie from 9:35 to 8:35, 9:55 to 8:55, etc. How would this be done with a macro for the entire workbook?
View 7 Replies View RelatedI have a column that has a date in it. If the day of the date = 07 (Ex: 08/07/2009 or 12/07/2009 etc etc) then I want to alter the font color of the ROW to be blue...or even just the text....
View 9 Replies View RelatedI have a workbook with several tabs. One tab is named for the year eg 2014. Annually, I open the workbook, rename to the new year and rename the year tab to the next year eg 2014 to 2015. All the dynamic range names update as expected. Sheet2(2014) becomes Sheet2(2015). However, a line of macro code does not change and errors out because the year does not change.
Is there a way to dynamically modify the '2014' sheet reference to reflect the renaming of the year tab eg to 2015 or to reference a helper cell named YEAR?
The cell referenced above R332C1 is dynamic.
I have tried writing the above line many ways based on internet research but no success. I suspect I can use a LEN function to actually modify the line of code but I'm not sure its even possible to alter a macro dynamically to meet my needs here.
i have loads of oval shapes on a sheet and i am trying to loop through them when i open the workbook and change the interior color to red. however i'm getting an error and i can't figure out why?
View 2 Replies View RelatedIs there a function or macro that can take a list of about 200 numbers and search for these numbers throughout the workbook, If 1 of these numbers exists anywhere in the workbook, it changes the color of the number or does something to the number where I will know this number is part of the exception list? The list of 200 numbers in non changing, however the numbers I want searched will change daily. There will also be worksheets added and taken away that contains these numbers.
View 9 Replies View Relatedis there any way by which you can alter the number of decimals in a custom format using just custom format process (ie no VBA).
For ex:
12 should appear as 12
12.1 should appear as 12.1
12.26 should appear as 12.3
Note 12 should NOT appear as 12.0
So point being adding the "m" is an irrelevance at this time - the key is to vary the decimal points essentially based on MOD(value,1) = 0
If you can (I'm really thinking you can't) let's extend it such that 12.1 should appear as 12.1 but 12.26 as 12.26
I know you can use [value>x] type rules but I'm guessing you can't use formulae in those rules ?
I would like to use a pivot table to manipulate my data. However, I need to be able to select data within my data set by a range of dates (usually a week). Is there a way to use a pivot table but reduce the data set by a user enter range of dates?
The date field is the first column in my data sheet.
I have a sheet used to process sales orders - about 500 orders per month x ~1.5Mb each. I want to save them as smaller files.
The sheets to save are full of vlookups linked to external sheets and tabs, other formulas, and macros - most of which do not need saving when the sales order is filed. There are lots of merged cells too.
I must save:
1. Cell values
2. Cell formats incl merged cells, borders, colour, font etc.
3. The row and column sizes
4. Print set ups - print area, margins, page setup, header footer etc - (Everything needed to reprint to same as original)
I think I need a "File save as" style Macro which opens a dialogue box for the user to nominate the destination folder (& allows the user to browse for it), and a new file name.
The original file name is "Job Sheet - Omlaw.xls"
The tab/sheet to save is "Front Sheet"
(There are two other tabs - neither of which need saving.)
All cells to be changed to "locked".
The sheet must be password protected in Excel. User to enter it - or not.
The saved file should be "write protected" if possible?
in my spread sheet i am trying to use the if function to compare the current time ( vb code used) to a time i enter to alter another cell.
for example
if A1(current updating time) = A2 ( time i choose) then
B1( some value ) = B2( another value)
the point of this equation is to have B2 update its value from B1 every day so today B2= 3 and B1 =4 then tomorrow B2 would = 4 etc , i just dont know how to do it.
I need to alter this code in order to do 2 things
1. to leave a blank line inbwteen Shop A's Apples, Shop A's Pears etc
2. I also wanted to total up the price totals....(see example below).So if the last price was in E4, the total of all those prices above would show in F5 ....
I have attempted to alter this example to simply check if the cells are not blank by replacing the "X" with "<>", but I get errors in the SMALL function--it looks like the IF statement is just returning an array with all of the values set to FALSE, and none of the values in the array call out a location of the next filled cell.
How could you alter your fixed XLSX file to check for non-blank cells instead of "X" specifically?
I need to use the Worksheet Change event in a particular sheet in a specified column which works fine if the data is already there in the sheet and then changed however,the data is in this sheet is actually a Sub-Set of a Main sheet i.e certain filtered records are being copied from Main Sheet and then copied to this IBSL Sheet.
After the data is copied I have to check each record manually and then categorize each record as Fresh , Rebooked , Cancelled , Tranch or On-Hold.....These 4 criterias are added in the Column 38 and the same thing has to be repeated in the column 40 , so when i change the data in the column 38 the same category has to be updated in the same row in the column 40..
But the problem is that the data is first copied from the Main Data Sheet into the IBSL Sheet using a Macro so then this even t gets fired and goes in the DEBUG MODE...
I need this to happen when i change the category manually..I am adding data validation at the same time while copying the data in to the TEMP sheet.
So what can be done to achieve..
Is it possible that when you press a command button, that the first thing it does is to execute the code assigned to another command button (IE in another sub).
View 9 Replies View RelatedI am playing around with the Forfiles command (being called from Excel via Shell)..
I can't for the life of me get it apply a second command (such as getting the file size of each file)..
For example:
VB:
Private Sub CommandButton1_Click()
Dim Z
Z = Split(CreateObject("wscript.shell").exec("cmd /c forfiles /P C:UsersapoDesktopTextFiles /S /M *.* /d
[Code]....
The end result being the filename and the filesize shown..
Excel 2007
I have this simple formula: =IF(SEARCH("ABC",BQ239,1)>0,"Found", "Not Found")
Instead of saying Not Found when the value is not in the cell BQ239 text string I get #VALUE! returned
how do I get it to say "Not Found"? I searching for a substring that can be anywhere in the string.
I need the macro to look at cells B9:B84 on the Sheet1 tab of the Cost Template. If it finds an x I need it to copy the 3 cells to the right of the x and paste them in a template. For example if it sees an x in cell B9 it would copy cells C9, D9 and E9, open the Purchase Order to the Detail tab, then paste it to cells B3, C3 and D3. It would continue looking for an x down to B84. So if it found 5 cells with x, it would give me 5 instances of the Purchase Order with 3 cells pasted into each.
I've attached my Cost Template and the Purchase Order it needs to copy to. In the Cost Template is a macro called Create_PO. This is what I was trying to alter to make this happen. I can't seem to get it right! FYI in case it matters, I had to change the Cost Template from .xltm to .xlsm in order to upload it on this site.
Is there any way to tell (from VBA) what GUI command is currently in progress or what the last command used was?
On a wish list perhaps: Application.LastCommand and/or Application.CurrentCommand
Let's say I'm in the "Sheet_Change" event and want to know what caused the change. Was the event triggered by "Delete", "Paste", "Keystrokes" ...
AutoCAD VBA has "Begin_Command (CommandName as String)" and "End_Command (CommandName as String)" events which I find very useful. Basically I'd like to emulate that to the greatest extent possible.
Application.Caller does not seem to work for the stated purpose. I've been searching for a workaround by means of reading the "Undo Stack". Numerous articles written by experts state that the undo stack is not accessible from VBA.
I've considered reading the text from the "Undo" button's caption but it just seems like such a hack ... not that this whole idea isn't a hack
How can I return name of a command button on click? I want to create some sort buttons on a sheet I will use regularly to speed things up. I thought if I could call each sort button by the column letter the button sits in I can use this in a single sub for all buttons
e.g the button sitting in column A is called "A". when i click the button, it returns it's name to a variable which I can then use to sort column A. I know I could create a seperate routine for each button but I was just trying to think of something neater.
I have a userform with a button on it named But1. Is it possible to store But1 into a variable then activate the But1_Click action using a variable?
Dim strButname As String
strButname ="But1"
Now, how would you activate the But1_Click method using strButname variable. With listboxes you can use the Control(strListboxName) methodology?
This doesn't work, but gives you an idea of what I am trying to do.
Controls(strButname)_click
I have a Vlookup which I want to modify so that it can become dynamic as the table array part of the vlookup will change.
So the basic vlookup is as follows:
=VLOOKUP($R$3,ATTRIBUTION_FACTSET!$M$60:$P$73,2,0)
but the data I am looking for wont always be in the range M60:P73.
So I tried to make it dynamic by doing the following:
=VLOOKUP($R$3,INDIRECT("ATTRIBUTION_FACTSET"&"!M"&U1&":P"&V1),2,FALSE)
The idea being that U1 and V1 would be numbers that can change so in this case U1 would equal 60 and V1 would equal 73
This vlookup is giving me #N/A and no matter how I modify it I cannot get it to work.
I have a sheet using VLookup to find EMail and Web addresses. I can get the address to show up but not as an active URL address. Is it possible to have the address "active" so I can click on it and activate the EMail or Web Site?
View 5 Replies View Related