Need a quick macro for the following in sheet 1
Convert data to proper for columns- E, Z and ADConvert data to upper for columns- A, I, X and ACHighlight Special characters with cell colour as Red for (!@#$%^&*:”<>+_’;\/?`~-)Find & replace % in the current sheet with blank.ran.
Our company's accounting system is physically split into 2 company databases, CompanyA and CompanyB. I'm trying to write a query which will pull the customer number and the accounts receivable balances from both companies into a single Excel table, for any customer with a total balance 0.
I have created the following query, which doesn't generate any errors, but is only successfully importing the data from the first company.
The CompanyA DSN is specified in the query ...
I tried to import web data from a textarea (look at example2.jpg) into excel and it always end up squeezing all the information into one single cell(spreadsheet.jpg).
I am trying to find a way to edit the imported data which is similiar to paste special or extract the information from the cell into a table, but I seem to waste a lot of time on this and going no where.
I'm Importing Columns from one sheet to another in the same workbook. imports good But:
1- Some columns with first records/rows blank do not import at all.
2- I have a column with some letter/numbers combinations and single numbers at times. Those single numbers are not imported. And half my data in that column are single numbers.
is there a way to change the case of a cell/column without having to use a formula, i.e. just like you would in Word?
The formula seems to be a huge pain and I have to do lots of quick case changes in a large document.
I have the following text in B3
The Average of the last 6 items in column A is R112.50
I want a formula that will make the first letter of the sentence in proper case i.e "The average" as well as "R" before the value in proper case i.e R 112.50
See example below ...
I found this bit of Worksheet_Change code to change the target area to UpperCase. This works fine.
If Not Intersect(Target, Columns(2)) Is Nothing Then
Set rng1 = Intersect(Target, Columns(2))
Set rng2 = Intersect(ActiveSheet.UsedRange, rng1)
For Each cell In rng2
If cell.Formula "" Then
cell.Formula = Format(cell.Formula, ">")
End If
Next cell
End If
I could not find anything telling me what the ">" means. I'm assuming that it is a special symbol/wildcard for UCase in VBA.
My question(s), is there a symbol for ProperCase so I can use the same code, just making it change the Target column to Proper? Also is there a list of the special symbols.
I use the applicaiton timer but it said time is 5 so what's the problems? Below i want to change to case construct.
Sub GreetMe()
If Time < 0.5 Then
MsgBox "Good morning!" & " Its now " & Time, , The Time
ElseIf Time >= 0.5 And Time < 0.75 Then
MsgBox "Good afternoon!" & " Its now " & Time, , The Time
Else
MsgBox "Good evening!" & " Its now " & Time, , The Time
End If
End Sub
this is what i did so far but still having problems with it with case construct
Sub GreetMe()
Dim morning As Integer
Dim afternoon As String
Dim goodevening As Integer
Select Case morning
Case Application.AutoRecover.Time < 0.5.......................
And last but not least, is there a macro where I can perform a "change case" on the titles of all my graphs to be using "title case"?
To recap, this is what you have all come up with so far:
Sub DoAll()
Dim wb As Workbook
Dim ws As Worksheet
Dim objCht As ChartObject
For Each wb In Application.Workbooks
How to change the lowercase letters to uppercase letters?
While using the userform, for example "name" is entered in the form.
My requirement is the values should reflect as uppercase letters like "NAME"
I needed to change the case of entries in range B8:B100 to uppercase, for which I used the VBA I have been using, and it works fine. I now require to also change the case of ranges C8:C100, D8:D100 and E8:E100 to propercase. So, I changed the script to:
View 4 Replies View RelatedI am trying to change string values of a name from uppercase to lowercase. The data is layed out as lastname then first name. As an example, I have the following values in column C:
ANDERSON MICHAEL
SMITH SUSAN P
JOHNSON PATRICK
JORDAN MICHAEL R
WOODS TIGER
CLINTON WILLIAM J
I am trying to convert to following (with "," between lastname & firstname):
Anderson, Michael
Smith, Susan P
Johnson, Patrick
Jordan, Michael R
Woods, Tiger
Clinton, William J
I have a start of how I think the code should flow but can't figure out how to do the conversion.
=upper("venkat") returns VENKAT
=upper(a1) returns the text in A1 in upper case
similary lower
proper will turn the first letter into uppercase
is this what you want;.
Terry <terrybetts11138@hotmail.com> wrote in message
news:dbl0dk$poa$1@nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com...
> Win XP Pro
> Office 2003
>
> Using "Excel" and wish to select any text in a worksheet to ALTER the case
> to either upper or lower.
> When using "Word" it is easy via the menu.
> Is there a menu driven option in Excel
of using a function for this purpose.
find the "add in" that converts the case text
from lower to upper in excel. i've already downloaded this before but can't
remember where from and it is much easier than creating a formula or a macro
I have found the code below that is very good and changes the case size to Upper, Proper or Lower. The problem is that when I select a cell, range of cells, column or row it changes the entire sheet instead of the selected range.
Code:
Sub CaseSize()
Dim myCase, rng As Range, r As Range
myCase = Application.InputBox("Enter" & vbLf & "1 for Upper Case" & vbLf & _
"2 for Lower Case" & vbLf & "3 for Proper Case", Type:=1)
If (myCase = False) + (Not myCase Like "[1-3]") Then Exit Sub
On Error Resume Next
[code]....
I want to use wildcards and it seems I cannot do it with case select, is it possible?
Its required because for the list of PSV's I have more than 2000 names and I would like to use *PSV* for them all instead of typing them all.
Here is the code below:
Option Explicit
Sub Colorize(Rng As Range)
Dim Cel As Range
Application.DisplayAlerts = False
Application.EnableEvents = False
[Code] .......
Is it possible to change and entire sheet to Upper case using the 'Upper' function?
We are need to change all the text to caps but can only seem to apply the function to one cell at a time and then copy it down for the column. This is going to be a fairly labour intensive process as we have many columns.
How can I make this code fire for column 2 AND column 3?
View 2 Replies View RelatedI have a workbook on which I want to change all the text from Lower to Upper case.
How can I do this without retyping .
macro that will change the case of a string of texts to proper except for prepositions (e.g. and, or, about, the, etc.).
View 2 Replies View RelatedIs there a way to change the text in a cell to proper apart from 2/3 letter words which I want to keep as upper? Basically can it ignore all words that are 2 or 3 letters long, but change all other words to proper text?
View 3 Replies View RelatedI need some code that will find the column with the header of "Gender" and change the values from "M" and "F" to "Male" and "Female" but I keep running into issues.
I want to use a Select Case statement so I can set all other found values as blanks.
I would like to format a row of cells so that when a word is entered into the cell it automatically becomes a capital.
I need the word to be capitalized so that I can use it in a custom function. The function uses the word from this cell and goes through a bunch of cases in determing how to classify the string.
I think more than one solution is possible and I would greatly appreciate some feed back, I've tried looking into turning all the letters of a string in my VBA code to capitals, or a way to format the cells, so that the string is already capitalized when entered into the VBA code, but I'm still a novice at VBA and unsure on how certain commands work.
here is a sample of my vba code.
Function WeightI(Shape As String, sDim As String, dLenFt As Double) As Double
Const pi As Double = 3.14159265358979
Const Ft2In As Double = 12
Const dDen As Double = 0.2835 ' density of steel, pounds per cubic inch
Dim aiStr() As String ' dimensions as strings
The spreadsheet I have designed retrieves stats and data from individual player pages and stores them in an excel workbook.
I have used 20 seperate web queries, to import the data for each of my players (from an online cricket simulation game). This works fine, but my problem is that I want to make it usable for other players.
Player pages are titled playerinfo.pl?playerid=1020, through to playerinfo.pl?playerid=1039.
Ideally Id like the user to enter his lowest playerid into a cell, and then create the web queries for that manager's players.
I'm not sure if this is a possible scenario, or if I need to use a repetition loop in VBA.
Im using Excel 2002.
In my worksheet there are ranges A3:C37, E3:E37, J3:K37 and P3:P37 that all contain text that I would like to automatically change to proper case once the user leaves any of the referenced cells.
I have tried various codes form this forum and searched for hours on the net for a solution to do this but no matter what I do/try nothing works (for long)
Another forum user did help me out with some code but there was an issue with column C, L & O (which are set as drop down lists) and when the code was put into the workbook these columns stopped working and froze the app.
I'm using Excel 2010. When I type 'true' or 'false' in any cell, Excel automatically changes to CAPITAL and Align Centre. I have to use the function 'lower' to change to lower case and manually to align to the left.
How to change the default setting.
I have a Excel database query which of which i import into Sheet 1. On a daily basis I need to edit this query and change a critea field to yersterdays date. Is there a way in which I could run a macro to change this query for yesterdays date without having to manually go into the query?
I have tried to run the macro, however I can only run this if I have a specific date in my code e.g. 11/03/2007 and not a formula to show yesterdays date.
I want whenever i select any dept in the B column say ID or Design... the value into the D column should change automatically...
say if i select in B5 as ID then D5 value should show me India
******** ******************** ************************************************************************>Microsoft Excel - resource_sheet_11_Dec_07_V2.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=ABCDE1Project(Client - Name- Length)Dept.Resource 1-Oct2 3Template 4 5New Course Sample Template ID 6New Course Sample Template ID 7New Course Sample Template DesignDesign Pool 8New Course Sample Template BuildBuild Pool Project Schedule [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I looking for a solution to change a Access Query Criteria using a VB code in Excel, without opening the Access DB and changing the criteria manualy?
My normal Job is it:
1) I Work all the time in Excel
2) At the end of the work in Excel, I need to open Access
3) Open a query (Called "MyQuery")
4) Change one parameter the Date: Between 01/10/2008 and 30/10/2008 (This for each month)
5) Run the query
I looking to get rid of points 2) to 5) by replacing with a Command button on an Excel sheet
Sub Change_Criteria_And_Run_Query()
Dim mydbase As Object
Set mydbase = CreateObject("Access.Application")
mydbase.OpenCurrentDatabase ("C:My doucmentsDB1.mdb")
mybase."MyQuery"."Date Criteria" = Between (Worksheet("Sheet1").Range("A1").value) and (Worksheet("Sheet1").Range("A2").value)
>>>>>The above line of code I have problems to get right!
mydbase.DoCmd.RunMacro "MyQuery"
End Sub
What I have is a database of measurements taken, where X-axis is distance across a surface and Y-axis is measuring deformation to that surface. Each measurement is actually a group of data points from one sweep across the surface on a certain day, resulting in a line plot for that group. What I'm trying to do is create a "checksheet" so that you can overlay plots from multiple dates on the same grid to compare how the surface has changed with each measurement. I've got the checksheet part working properly, and I can toggle things on/off as I like as long as my data doesn't change.
The rub is that this data is gathered via query, and each measurement doesn't have the same # of data points. If I update the query to look at a different date range, the plots are now off because the old data range doesn't match the new data.
Is there any way of changing the data range when a query gets updated, or a way to tie it to a function?