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?
How can I extract the first word only from a Textbox string?
View 2 Replies View RelatedStarting 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 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 have an excel program that is supposed to count word instances in a word document. I can't seem to find the right declaration for a word document.
For example to declare a workbook in excel its
Dim wb As Work Book
I've tried
Dim doc As Word.Document
'or
Dim doc As Word.Application
as shown in some of the forum posts, but an error user-type not defined keeps displaying.
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 am attempting to format some TextBoxes from within a For/Next loop. I need a way to check which TextBox is the active TextBox in the loop. Using i as the variable, I came up with this code snippet: Me.Controls("TB" & i).Text = Format("TB" & i, "mm/dd/yy")
If i = 3, this gives me in TextBox3 (which is called TB3) the text 'TB3' and not the value of what is in TB3. It has got to bo something simple, I just can't see it!!!
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..
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 it possible to export Excel cell contents to Word fields in a protected Word document? For example...
What code would be needed to tell Excel to open up, copy and export the contents of A2 in the active sheet of a workbook to "Field 2" in a Word document named "Report 01" and then put the contents of B2 to "Field 2" etc?
Do both applications have to be opened up at the same time or is Excel able to open up Word on its own? Will the macro be able to....
1. Automatically open up the correct Word document?
2. Look ONLY in a certain folder for the "Report 01" Word document?
or
Bring up a "selection" box that allows you to select the document you wish Excel to export it's data to?
3. Close and save the Word Document without any user intervention?
I need to count how many times the word Test is in the range B4:H9 with
Range N2 = Test the formula below works if Test is only in the cell once.
=COUNTIF($B$4:$H$9,"*" & N2 & "*")
But I have data in cells like below, this is all in one cell, so how would I have it count all the times test is in the range when some cells have test 2 or more times in a single cell?
5
Test
8am-2pm
Test
5pm-10pm
How can I use the IF function, to make entering a word, space then word display correct and incorrect if not.
View 13 Replies View RelatedI need my macros to search for the word "Cancel" or "Cancelled" in columns "T" and "U". Once found, I need the macros to make that entire row an opaque shading.
There will be other wording in these cells that contain "Cancel" or "Cancelled". Is it possible for the macros to search in the sentence and find the words "Cancel" or "Cancelled"
I started on the code below but am stuck.
Have problems using find and the Dictionary
What Im trying to do is find a certain word in a string then return the number associated with that word
I am trying to automate the below process:
1. I have a excel file with Japanese words in column A and their English equivalents in column B.
2. I am trying to create a Macro and assign it to a button. On pressing the button Macro should be able to:
3. open a form where I can enter location of a word file.
4. Macro should open the word file specified in (3).
5. Macro will pick up first Japanese word from excel file (Sheet 1 - A1)
6. Search for that word in Word file
7. Replace the Japanese words in Word file with their English equivalent from excel (B1)
8. Then it should search for second word (A2) and replace with its equivalent (B2) and so on till it reaches last filled cell in excel file column A.
After a lot of search I could find a code from net (Below), made a few changes, but it is not working.
========================================================
Private Sub OK_Click()
' Requires a reference to Microsoft Word xx.x Object Library
Dim sFile As String
[Code]....
I would like to have a formula find a specific word in column L and return the sum from column E for the same fund from column C.
I tried using =SUMIF(L:L,"*annual*",E:E) but that gives me the sum of annual for the entire column. I need to be able to specify the fund.
word "annual" also appears in "semi annual" so I need to be able to separate the two.
I want to say if the word 'suitcase' is in column W (any row), place the word 'suitcase' in column Z, same row.
View 2 Replies View Related