1. D

    Using MID with IF statement

    Hi my 1st time posting here and look forward to all your support and help! I am trying to use MID from Column A1 onto B1 and then restrict/data validate Column B by preventing special characters. I want it to return in column B the MID function result but if it contains special characters it...
  2. G

    Canot change format of a cell

    Scratching my head on this; I copied a date from the web 'Jul 3 2018' and when I try to format(excel 2007 PC) it in VBA to dd/mm/yyyy but it doesn't change. I even tried paste special: Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone...
  3. G

    VBA to Clear Formulas out of a Worksheet

    Hello All. I have the following Code: Sheets("NonFormat").Select With Selection Cells.Select Selection.ClearFormats Selection.Hyperlinks.Delete Application.CutCopyMode = False Range("Z1").Select Range("A1").Select End With This is working in place of pasting special...
  4. M

    VBA Paste Special

    Trying to automate the copying of some rows, fails on the paste special section, and I cant figure out why!! Sub test() Dim LR As Long, i As Long With Sheets("Analysis") LR = .Range("A" & Rows.Count).End(xlUp).Row For i = 12 To LR If .Range("K" & i).Value = "CORB01" Then...
  5. K

    How to stop "Skip blank cell" when special pasting

    I'm using Excel 2016 on windows 10. When I copy a range of data I want to "Special" paste, it excludes the blank cells in the range and then the pasted data is misaligned (column wise). I have searched for a solution, but all I can find is how to turn ON Skip blank cells. Is there a way for...
  6. N

    VBA Function - IsNumeric Allows some Special Characters

    Hi, 1st time on here so bear with me. I have written the following code, to ensure only numerical values are entered in a VBA form input box; Private Sub OperatorNumber_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) If Not (IsNumeric(OperatorNumber.Value) = True) Then Call...
  7. B

    Highlighting special characters in excel

    I am trying to find a VB code that will highlight a cell that has a special characters with the exception of the following ,-.:;{}[]_/,()*? ?@&
  8. A

    Using paste Special in Sheet taking a lot of time

    I am preparing a dashboard whose first row contains the formula which needs to be dragged down . It takes almost 30 minutes to do a paste special . is there any way to reduce this time . The Sheet has 182 columns and a total of 8000 rows . Takes almost 30 minutes daily.
  9. A

    Extract text only within a string, and numbers only within a string.

    Hi, I have a jumble of text string where in one column I have to extract the text only (no numbers and special characters) while on the other column I have to extract numbers only (no text and special characters) <tbody> String Text Number aaabbb!.,'1234222 aaabbb 1234222 abc@gmail.com...
  10. K

    Paste Special HTML option missing

    I am on Excel 2010. I don't know if I ever had the HTML option in my Paste Special drop down, but the only options I have are Unicode Text and Text. Is there a way to add the HTML option?
  11. V

    Special paste as link function / operation unavailable

    <!--[if gte mso 9]><xml> <o:OfficeDocumentSettings> <o:AllowPNG/> </o:OfficeDocumentSettings> </xml><![endif]--><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:TrackMoves/> <w:TrackFormatting/> <w:PunctuationKerning/>...
  12. C

    Searching for patterns of text with VBA

    Hi there, How do I search for a pattern of text in cell using VBA? I've looked into the instr and find method but this still eludes me. For example, I've got a list of clients with special IDs attached next to their names. (i.e. John Smith ABC1234). How do I search the cells to determine...
  13. G

    VBA function assign value to date based on distance

    Hi everybody, I have a list of dates (Events) and another list of dates that fall in between those (special events). I want to do a vba function that finds the special event in the calendar, and add to each calendar date 10 divided by the number in between the 2 event dates that contain the...
  14. A

    Highlight everything except English letters A-Z

    Hello , I want either Excel Formula or VBA code so that it can highlight everything in a column (Numbers, special characters, spaces, all foreign alphabets except English letters.) with some color(Red) and cell should be highlighted with Yellow color. Eg. Cell A1 contains word like "World@1234"...
  15. J

    Macro to paste special into certain cells

    HI All Been a while since I have been here - moved job and lost my excel go to person. Anyway, not sure how to do this. I have a report that I copy data from, paste special as text into various different cells depending on days of week on sheet 1. The cells are d7, d37, d67, d97 and d127 all...
  16. I

    Deleting Special Characters

    Hi Good Day everyone! I have an excel file that needs to remove all special characters except for the following 1.)A-Z & a-z 2.) 0-9 3.), . - _ é É I have found a vba code but I really cant update it cause I might break the file. This code only removes the characters under xChars. is...
  17. L


    Hi A1=left("123-456-7890",3) The result A1 = 123 (but as a text, aligned to the left side of the cell). I changed the format to number, general etc it is still text. I learnt if B1=1 and I copy B1 and Paste Special (multiple) A1 then A1 will be 123 as a number (aligned to the right). I...
  18. P

    Find special characters in a cell and highlight with vba

    Is there an easier way of highlighting cells that contain a special character (!~`@#$%^&*()_+-={}|:"<>?[]\;',./) instead of listing everyone separate like below? There can be letters and numbers along with the special characters. If ActiveCell.Offset(0, 4).Value Like "*.*" Then...
  19. L

    paste special + skip blank

    Hi I wanted to use paste special option. So I click on a cell which has value A1=2. I want to paste this cell on a column, so I select Paste special then "Add" then "Skip blanks". but excel added number 2 to all cells including the blank cells? Why is that? Thank you.
  20. T

    Copy Paste Special

    I am at a loss as to how to get this code to paste special since it is from a calculated field: Any advice? Sub Copy7() Worksheets("2018 PROACTIVE COA").Range("E25").Copy _ Destination:=Worksheets("CAREMORE").Range("C3:C6").PasteSpecial _ Paste:=xlValues End Sub

Some videos you may like

This Week's Hot Topics