1. Rijnsent

    Overflow - why?

    I'm a bit stumped: why does VBA behave like this? See the Sub below: when I simply Debug.Print 60*24*2*60, I get an Overflow error. So apparently VBA treats that multiplication as an integer and therefore fails as it's too big. But why do the tests after that fail and why do the last two...
  2. J

    Runtime Error 13: Type-Mismatch

    Hi, I have a code from Stackexchange, which is giving a runtime error 13 with 2 of the 3 supporting examples. And, I have not been able to decode why. Sub Example 3 executes but Sub Example 1 and Sub Example 2 fail. Need help in getting it rectified. Function MultiSplitX(ByVal SourceText As...
  3. I

    InStr() within a Range, mistmatch error

    i have a range of rows that contains text. I want to check if a string exists within this range. but i keep getting errors what is proper syntax? Rng = Range("a1:a200") if Instr(Rng, "All Formulas") then if Instr(Rng.text, "All Formulas") then if Instr(Rng.value, "All Formulas") then none...
  4. G

    Format(1000, "##0.000E+00") not working to format as engineering type

    When I use the custom number format below, I get 100.000E+01. Every help file and website I can find says the result should be 1.000E+03 which is what I want. Debug.Print Format(1000,"##0.000E+00") ' Yields 100.000E+01 Debug.Print Format(1000,"000.000E+00") ' Yields 100.000E+01 Shouldn't these...
  5. B

    Get email recipients (FROM / TO / CC / BCC) from outlook mail item

    My code I have creates a table from getTable method but I have recently learned there is no MAPI property to return the email addresses of these so my table columns are useless for this part. It seems the only way to do it is via the mail item object. However, I notice with my code below which...
  6. Y

    Outlook VBA: Save attachment, open excel, run a macro and resend out as an attachment

    Hi, I am still relatively new to VBA coding though have been running files with inbuilt macros. As my knowledge is limited to using existing codes and modifying from there, I hope fellow members in this forum can guide me through my issue below. I am trying to complete a VBA code in Outook -->...
  7. most

    Retrive adress from selection

    Can anybody explain why this happens? With the third case I expect the result to be $A$12 $B$13 not $A$12 $A$13 Sub Sel() Debug.Print "C:" & Selection.Count Debug.Print Selection(1).Address Debug.Print Selection(2).Address Debug.Print "---" End Sub The grey areas are the cells...
  8. M

    Unsure on how to loop SearchRange

    The code below works fine and does exactly what i want it to do, however what is the best way to loop it Sheet "New" has approx 3000 lines of data, the code below finds the first instance of SC which in this argument is 999, after its copied those values to "Database" how can i get it to...
  9. A

    Set Read Cell Attributes

    Dim x As Range Set x = Worksheets("Sheet1").Range("C2:C5") Dim cell For Each cell In x Debug.Print cell.Font.Color Debug.Print cell.Font.Name Debug.Print cell.Interior.Color Next Why does Debug.Print cell.Font.Color always return 0 regardless of the actual font color ? This is giving...
  10. S

    Date Issues, syntax, order, format

    Hi all I am westling with a date issue in vba, I know it is to do with the way excel manipulates dates, but I just cant get there. I derive two strings from a user form input, one is the month (selected from a list Jan to Dec) and the other a year in two digits (from a list from 15 to 25...
  11. G

    Combine two PDF into one Separate PDF and save in particular path Using VBA

    Hi Everyone, Here i am trying to combine two PDF sheets using Adobe, where the below code runs perfectly but Sub Combine_PDF() Dim x, lastrow As Long Dim arrayFilePaths() As Variant Set app = CreateObject("") lastrow = Sheets("Combine_PDF").Cells(Rows.Count...
  12. J

    How to not scramble punctuation symbols while scrambling words?

    I am unable to retain the position of a punctuation in the text while performing Cambridge Transposition (which scrambles every word in the string except the first and last). The reference code is: Sub v(strText) m = 1: Z = Split(strText, " "): j = UBound(Z) For u = 0 To j t =...
  13. 2

    Overflow Query

    Hi, I was hoping someone might be able to explain this to me. When I execute the following code (which simply calculates a value, and using debug.print, outputs the result), I get a Run-time Error '6' Overflow message. Sub calc_ulation() Dim x As Long x = 1000 * 60 Debug.Print x End Sub...
  14. R

    This should be easy, frequent appeared #

    Hello Mr. Excel users! Function abce(mySheet As String, mySELEC As Range) As StringApplication.Volatile Dim x As Range For Each x In mySELEC Debug.Print Sheets(mySheet).Cells.Find(what:=x.Value, MatchCase:=True, LookAt:=xlWhole).Row Next x End Function -above code is working...
  15. R

    Printing Dictionary Values

    Hi I can print Dictionary values to the immediate window by running the code below, but I wanted to know how I print "all" the Dictionary values into an Excel sheet? Public Sub DictionaryExamples() Dim exampleValues As Variant Dim i As Long Dim aKey As String Dim aValue As...
  16. P

    how to display value of a For Loop

    Private Sub cmbSDPFLine_Change() Dim i As Integer Select Case cmbSDPFLine Case Is = "SDPF - Line 1 " With Me.ListBox1 .Clear .List = Sheets("1st Shift").Range("C7", Sheets("1st Shift").Range("C29").End(xlUp)).Value...
  17. P

    how to debug.print datepicker value

    As the title states, how do I get the value of the date picker by debug.print. Thank you
  18. M

    vba shape redraw

    Hello all!! In Excel, if I add a shape to a sheet and then delete it, the shape object still remains in memory. Is there a way to redraw that shape? Please see the code below for reference. Sub DrawTest() Dim x As Excel.Shape <code style="margin: 0px; padding: 0px; border: 0px; font-style...
  19. G

    VBA Recursive Folder search for specific Folder and File combo

    Hi I'm trying to find a set of files that contain data I want to manipulate. End goal is to collate all the data from each file matching my criteria. However, I'm struggling at the moment as the function I'm using to search is crashing out. I believe that it's failing due to either a file...
  20. H

    Porblem to line

    Hi, How to resolve!Ai8CrEskdewXm1UXeAKPcd2mrkZJ due to last line below Workbooks.Open Filename:=fl fn = ThisWorkbook.FullName Debug.Print fn Application.Workbooks(fn).Activate

Watch MrExcel Video

This Week's Hot Topics

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back