I have thousands of path names in a spreadsheet that were converted to hyperlinks, but my client wants to see only the filename in the hyperlink, not the whole path.
I created the hyperlinks by applying the following to the column containing the file paths: ....
I have come up with this to Trim all of the data from rows 2:30 removing any trailing spaces after the last word in each cell. The macro takes a couple of minutes to run have I got something wrong that is making it run slowly or does the Trim process just take longer?
Sub TRIM_RANGE()
Dim myRange As Range
Dim myRow As Range
Sheets("CAMPAIGNS_2007").Select
Set myRange = Range("2:30")
If myRange Is Nothing Then Exit Sub
Application. ScreenUpdating = False
myRange.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
For Each myRow In myRange.Columns
If Application. CountA(myRow) > 0 Then
myRow.TextToColumns Destination:=myRow(1), _
DataType:=xlFixedWidth, FieldInfo:=Array(0, 1)
End If
Next myRow
Application.ScreenUpdating = True
End Sub
I am trying to update a project tracking sheet at work and I am having trouble. I have a list of construction projects (approx 130) along with details on the project, one detail being % completed. I am trying to create a summary of the projects at the bottom of the page, showing the total value of all projects, according to the % completed.
<10% is ANTICIPATED
= 10% is AWARDED
>10% <90% is IN PROGRESS
>90% is COMPLETED
[code]....
I'm trying to trim all the data in my worksheet in order to prepare it for several steps of further analysis. Running the below code will cause an error that I don't understand. It says:
Runtime error '1004': Application-defined or object-defined error
the code is (error causing line indicated in comments):
PHP
Sub trimAll()
Application.ScreenUpdating = False
Dim Rows As Long
Dim Column As Long
Dim i As Integer
Rows = ActiveSheet.UsedRange.Rows.Count
Column = ActiveSheet.UsedRange.Columns.Count
For i = 1 To Column
Columns(i).Select
Selection.Insert Shift:=xlToRight
ActiveSheet.Range(Cells(1, i), Cells(Number, i)) = "=TRIM(RC[1])" ** ERROR **
Columns(i).Select
Selection.Copy
Columns(i + 1).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns(i).Select
Selection.Delete Shift:=xlToLeft
Next i
Application.ScreenUpdating = True
End Sub
I have a sheet. I want trim all the data in cells
I will select particular range and that range should get trimmed and It better to turn in colored which soever cell get trimmed.
I use the If - Then statement in VBA to determine a condition which works perfectly fine for the particular cell I reference to, but i need it to apply to all the cells i.e. cells A1:A10?
Sub MACRO1()
If Range("A1") = "PAYE" Then
Range("B1").Formula = "=C2 * .128"
End If
If Range("A1") = "LTD" Then
Range("B1").Formula = "=(C2 - 100)* .128"
End If
End Sub
Sam
I want to apply a simple formula to a number of cells in an existing workbook that already contain values E.g.
My column contains the values
5200
1600
4376
in separate cells
I want to divide each value by 1.2 - without having to change every individual cell - can it be done?
I am using two separate workbooks. I am tranferring dates into a new spreadsheet to track projects dates for milestones.
The formula I am using is this: =IF('[BBBBB Dates as of 9-9-13.xlsx]Sheet1'!$CQ$4="", VLOOKUP(W54, '[BBBBB Dates as of 9-9-13.xlsx]Sheet1'!$1:$1048576, 94, FALSE), VLOOKUP(W54, '[BBBBB Dates as of 9-9-13.xlsx]Sheet1'!$1:$1048576, 95, FALSE)).
I would like the cell to turn green if the last part of the formula is true and stay clear if the first part of the formula is true.
I also need to add color beyond just that. I was attempting to apply conditional formatting but am a bit stumped. I want the green to be maintained regardless of other formatting. I would also like to apply to cells that are not color coded green:
Red - if the date is overdue
Yellow - if the date is within 7 days
Otherwise, leave the cell color as clear
Is this possible with conditional formatting? I am not at all proficient in VBA...
Is it possible to apply Filter utility on Column Cells that are 'coloured'?
View 9 Replies View RelatedI am working with a spreadsheet and rather new to be VBA. How do I select a range that only has data. I currently have the following macro, but when I run it, it checks every cell in the active worksheet which cause the application to hang. I would like it to automatically select only cells that have data in them ignoring all empty cells. I need this to be an automatically process running without the user selecting a range of data.
Here is my code ..
Supposing you would like to find the median of numbers in column D, but only for rows that have the word "Jones" in column A? Is there a way to do this? Obviously I know I could do a sort and simply specify the range myself, but we're dealing with nearly 2000 rows and a LOT of different values in column A
It occurs to me that it would be even better if I could ask excel, in effect, to identify every separate text string in column a and then find the median of the corresponding numbers in column D. In other words I wouldn't have to type any strings from column A at all.
If you have a range of cells with values in them (Q2:AZ2), you can use conditional formatting to identify the top x% of the group and it would essentially treat each cell in comparison to the group. I need to identify if a cell in the group is greater than 3*Stdev(Q2:AZ2). Do I have to create conditional formatting for each individual cell or is there a way to apply a function to the group that would recognize each cell the same way that the canned conditional formatting rules work?
View 1 Replies View RelatedMy requirement is to remove the spaces in the text in each cell in a worksheet. i have used TRIM function for this. Sheet1 will have the imported data and sheet 2 has the formula to TRIM. i have an issue when using TRIM on date format. I have used formula TRIM(sheet1!Ax) for columns 1,2 and 3 and TRIM(TEXT(pcload!D1,"mm/dd/yyyy")) for column 4. But the blanks cells in sheet 1 have a value of 01/00/1900 in sheet2. I need the blank cells to be displayed as blank in sheet2.
Sheet 1:
82909U
AB
121
[Code]....
=IF(Z2="","",Z2&","&IF(AA2="","",AA2&",")&IF(AB2="","",AB2&",")&IF(AC2="","",AC2&","))
I'm using the above formula to join text from columns Z through AC, separated by a comma. I now want to remove the comma at the end of the new string. Also, I would prefer it if the four cells were separated by a slash rather than a comma, but when I simply replace the commas in the formula with slashes I get an error.
how can i do the following using VBA
making each cell in column A added to each cell in column B and the result will be in the column C
for example
c1=A1+B1
c2=A2+B2
c3=A3+B3
... etc
i know i can do that simply without vba code but I just want to use this method to implement more complex formulas .
how do I apply a formula I entered into one cell to all the cells in that column, without going through the tedious process of typing the formula in again in each cell ? I've done it before but don't remember how.
View 2 Replies View RelatedI was messing around with some Macros, as an issue came up where clients were pasting over Data Validated cells (thus removing the validation). Somehow though, the feature where you can select a cell, then pull the active cell down through the column (and then can copy, fill series, etc)has become disabled. I deleted all the macros, and turned them off in the Trust Center, but when I click on a cell the box and plus sign still won't display for me to pull the cell down the column.
View 2 Replies View RelatedSo I would like to automate the following, as doing it by hand takes a lot of time. I have searched the whole internet and finished empty handed.
Start from cell E10
Select cell
Move 3 cells down
Select cell
Move 3 cells down
Select cell
Move 3 cells down
Select cell
Move 3 cells down
Select cell
Move 3 cells down
Select cell
Move 3 cells down
Select cell
Apply double entry conditional formatting
Move 3 cells down
Repeat above until cell E5000
I'm trying to find ways of applying a formula in condition formatting to find entries which don't follow a certain format.
to be clear, this is for a reg plate of a car. There are two styles of formats. The X's represent A-Z and # represents numerical values
1. XX##XXX
2. XX####
find a way of applying two test cases (non-simultaneously) so that cells highlight if they don't follow either of the above conditions?
I have Excel 2003 & am trying to design a template for keeping track of my design products. I have several rows for links to previews, downloads etc. I use the template when placing merchandise in stores so I can copy/paste the info where needed. I want to copy links to a cell but have them be non-clickable. If I paste the link & then right click the cell & choose remove hyperlink I get non-clickable text, however I lose the original formatting for that cell.(left/center/wrap text).
View 2 Replies View RelatedI have many hyperlinks that are to blank cells, the cells may be blank now but can contain data at any time.
When the hyperlinked cell is blank the active cell displays a Zero (0), is there any way to make the cell appear blank like the hyperlinked cell?
I'm creating a table to store a large amount of data that will be continously updated over time. The table however will be viewed from a website, not as an Excel document, and I found a great add-in that generates small, clean HTML code to that end.
I'd like to do something else a little more tricky however ( for me ). I'd like every item in the first column to automatically hyperlink to a predesignated URL, and then parse the contents of that cell onto the end of the web address. If the cell contained multiple words, it would parse "+" between each word to create the proper address. The hyperlinks will be fetching database information from an exterior website.
For example, Column1 Row1 might say "Dog", so a hyperlink would be generated to [url] Column1 Row2 might say "Red Corvette", so a hyperlink would be generated to [url]
I have a workbook with dozens of filled cells.
The cells contian the file names: "filen_name.extension" (for example word.doc)
Also I have these files located in some directory.
Is there any possibility to add simoltaneously links to the cells which contain the appropriate file name.
I have a spreadsheet and within the first sheet there are lots of hyperlinks to other cells within sheet 1 (my template).
I have made a small macro which very basically makes a copy of sheet 1 (my template)
The hyperlinks in the template are explicit and as they are copied to sheet two, they still reference back to cells in sheet 1. I need the hyperlinks to be relative, so that when I make a copy of the template the hyperlinks are copied and make reference to cells in the new sheet. I cannot work out how this should be done
I tried to learn VBA about 4years ago, so my knowledge as died. I hope this question has not come up before, i did do a search before. I wish to create a new hyperlink based on a activecell eg. A1=SN0001 then hyperlink will be SN0001. Only thing i can remember how to do is record marco, but ofcourse copying the cell will return a hardcoded value.
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="1"
Where address is :="1" how do i paste the activecell. I have 9000 cells to hyperlink.
I have a worksheet where rows are continually hidden and unhidden.
When the code to hide or unhide the rows runs I would like to have a macro that sequentialy numbers/labels the visible cells in column D that contain hyperlinks.
i.e the first visible cell with a hyperlink will have it's text changed to "Table 1", the second visible cell's text will become "Table 2" etc. The hyperlink should not be altered when this happens.
Im trying to add hyperlinks to cells using an array but im getting "run time error 424 - object required" error. THe code im using is:
Code:
Sub hyper3()
' not WORKING - ARRAY to add hyperlink to cells
Dim rngIn As Range
[Code]....
i'm trying to automatically create hyperlinks in some cells from mail addresses located in another column. this is what came out:
View 2 Replies View RelatedI would like to have a simple basic VBA trim function with no complications at all. Just a simple trim vba code like:
=trim( Selected/Active cells)
But I just couldn't think of how to do it, even with recording.
Sample of Column A:
Sat 15th Sep 07 14:15 Bath Rugby 29 - 15 Worcester Warriors Recreation Ground 10,010
Sat 15th Sep 07 15:30 Harlequins 35 - 27 London Irish Twickenham 39,400 Report
Sat 15th Sep 07 18:00 London Wasps 19 - 29 Saracens Twickenham 39,400 Report
Sun 16th Sep 07 15:00 Bristol Rugby 13 - 26 Leicester Tigers Memorial Stadium 8,125
Sun 16th Sep 07 15:00 Leeds Carnegie 24 - 49 Gloucester Rugby Headingley Carnegie
Sun 16th Sep 07 15:00 Newcastle Falcons 33 - 12 Sale Sharks Kingston Park 5,859 Report
Trying to display in column B and C:
Bath Rugby Worcester Warriors
Harlequins London Irish
London Wasps Saracens
etc....
Because of the varying length of text each time,
i'm having problems doing this.
Tried =MID(A1,FIND(":",A1)+4,25)
but obviously get extra text other than team name.