vba excel

  1. V

    Error 4605 - this method or property is not available because the document is locked for editing

    This is to copy a range from Excel and send by email. Application.ScreenUpdating = FalseDim Rng As Range Dim xInspect As Object Dim pageEditor As Object Set myApp = New Outlook.Application Set myMail = myApp.CreateItem(olMailItem) Set sh3 = Sheet2 sh3.Unprotect Set Rng =...
  2. N

    Count the total lines in all files in all subfolders

    i want to use vba to count all the lines in all the files in all the subfolder of a directory and display them in a cell. how do i do that? i found a code working for counting the lines in a single file. but i dont know how to construct the loop to apply this to all the files in all the...
  3. I

    find and replace

    Hello, I have excel file with multiple tabs and I need to find text that contains "med ctr" or "med center" or "med cent" and replace it with "medical center" and this needs to be done on every sheet in the workbook. I found a couple of different codes but none of them works the way I need or I...
  4. U

    How to fix "Compile error" displays, "Variable not defined"

    Question for you. Sheet 1&2 name titles need to be changed with the following titles: Sheet1= PrePaidReport, Sheet2 = NewFormat, after I made the changes inthe VBA Script, Sheet titles change to capital letters, then I get, "Compile error" displays, "Variable not defined".
  5. H

    How to ensure cell content follows defined formatting rules

    Hi guys, Need a bit of help here. I am trying to put in some controls in an Excel sheet to ensure data entered follow a particular formatting style. I am hoping to do this via either data validation or some sort of conditional formatting to show conformance. The content of the cell needs to...
  6. P

    Total Row cell value should not be grater than 4.

    Hi, I am trying to write a code where if Total row cell value is more than 4 so it should given an error msg box and restrict the user to do entry. i am using this code <code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit...
  7. H

    Search 2 criteria in ListBox in UserForm after it is populated

    Dear All Masters, My Code: Private Sub Search_Click() Dim x1, i As Long, ii As Long, iii As Integer x1 = [myCar] Application.ScreenUpdating = False With ListBox1 If TextBox2 = "" Then .RowSource = "myCar" Else .RowSource = "" For i = 1 To UBound(x1, 1)...
  8. F

    VBA connect two columns both ways

    I have an invoice master template and in this invoice, as seen in the attached picture in the link. I have several columns. Column B (Item Description) is data validated with a list and then the other columns contain vlookup formulas to find corresponding information from a product list...
  9. M

    Loop through first cells of selected merged ranges only

    am trying to give the user the option to do simple arithmetic operations on selected cells. The thing is that most cells are merged ranges. I got the following already but the problem with it is that it loops through all cells while I only want it to only affect those cells that are not merged...
  10. A

    Max value in Excel.

    Hi guys, This is the macro that i create to insert a new number. Note that I have more than one sheets in my workboooks. In each sheets, there is a column called line reference. The line reference is defined as for exemple A050 or A001. It goes from A to F. For exemple we have A01 sheets, A02...
  11. A

    Locate duplicates and sum the two columns with different values

    Hi, Need help to add / alter the code. Need to sum the values on column 4 & 5 by locating duplication on column 1. Say for an example: <tbody> Consumables 31222 NY <tbody> 698.2 </tbody> 77.6 Consumables 31222 NY 123.4 445.9 Consumables 31222 NY 554.21 23.5 Spares 31119 NY 33.4...
  12. T

    Error number 48, error loading dll

    I build a tools that copied from ron de bruin win tips and adjust it according to my need. https://www.rondebruin.nl/win/s1/outlook/saveatt.htm But after i changed my pc (before using win 7 and office 2007) and upgrade the os, it doesnt work any more. Now im using win 10 and office 2010...
  13. A

    Evaluate formula using a variable as range

    I am trying to evaluate a formula with this code Sub test() Dim myRange As Range Set myRange = Worksheets("Sheet1").Range("A1:B10") 'answer = Application.WorksheetFunction.Max(myRange) answer = [SUMPRODUCT((LEN(myRange)>0)*1)] MsgBox answer End Sub But I getting an error...
  14. C

    For Each Loop VBA Code Running Very Slow

    HI I got the following code from a forum a while back. It finds merged cells, un merges them and fills in the cell value. [CODE][Sub UnMergeFill() Dim cell As Range, joinedCells As Range For Each cell In ThisWorkbook.ActiveSheet.UsedRange If cell.MergeCells Then Set joinedCells...
  15. J

    VBA to delete criteria is deleting my first row regardless of criteria.

    Hello folks I worked out a function and a procedure to help me find the last row and then delete filtered criteria. If there is not criteria, then leave the data alone. However, the procedure does filter and delete the data based on the criteria, but if there is no criteria instead of leaving...
  16. A

    VBA code to copy specific cells in column from one workbook and paste to another

    Hi, Sorry if this question has already been asked but i think I do have a specific case. I'm willing to copy data from column C to column O from a first workbook and past it to another workbook from column A to column M as below describe ; Workbook named Doc1 and worksheet named Query (copy)...
  17. Y

    VBA msgbox

    So i made this sub that makes a new sheet called "Statistics" in which there are some pivot tables. I made it so i was able to overwrite the sheet if it already existed. Now i'm trying to figure out how to make a msgbox that asks the user if he is sure that he wants to overwrite the sheet, if it...
  18. S

    Error 1004

    Hi all, I'm trying to select a range of cells in a data sheet defined by integers k and l. I need to select that range before I input it into a pivot table (code not shown here) When I use the .Select property I get a Error 1004. Any advise would be welcome here. ActiveSheet.Range(.Cells(k...
  19. C

    VBA - Send email with attachments to range (row by row)

    Hi, I have a spreadsheet which I seek to list client email address details etc. Due to a shortfall in our finance system. I seek to email attachments based on this info. I am having difficulty getting code to attach the specified attachments to emails (attachments are specified in Columns...
  20. O

    VBA code

    Hi Guys, Please help me on below query. I need a vba code for below example. If there is an excel formula then that is also okay. Question: So if there are numbers on Row 3(see table below), I want it to return the text from Row 1. From below example. I want the code to return this. "Dec...

Some videos you may like

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 MrExcel.com.
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 "mrexcel.com".
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