vba & excel 2010

  1. I

    pi>Visible <Type Mismatch>

    Respected gurus, experts and MVPs, I have written the following code for the pivot table and it is generating pi.Visible <Type Mismatch> error. Sub piVisibleMismatch() Dim lastrow As Long, endrow As Long Dim Psheet As Worksheet, Dsheet As Worksheet Dim Pcache As PivotCache, Ptable As...
  2. tlc53

    VBA Code - Closing Statement Wrong Perhaps

    Hi, I have the below code which is returning an error, I believe near the bottom. It says I need to "End With" but when I put "End With" before "End Sub" it says it wasn't expecting "End With" :banghead: I'd really like this to run without any problems. Can someone please show me where I am...
  3. tlc53

    VBA Code - Delete rows once column A no longer numbers

    Hi, I have a set of data which is exported into excel and I would like to automatically clean it up, so that it can be analysed. Once I select the entire data and sort it by column A, smallest to largest, it is then sorted by ticket number. I would then like to delete the rubbish on the...
  4. G

    Dynamic print range VBA

    Hey there, I am trying to create a macro that will "read" the range from a cell such as "02" (A1:M39) and export that range as a PDF, however I have only been able to set the range directly in the VBA code, is it even possible to have it read a range that changes dynamically? For comparison...
  5. tlc53

    VBA Code Amend - Protect All Worksheets, Allow Formatting of Columns & Rows

    Hi, I found this neat VBA code to protect / unprotect all worksheets without having to do each one manually. See below. The only problem is, it only allows; - Select Locked Cells - Select Unlocked Cells Whereas I want another 2 options to be included as well; - Format columns - Format rows Does...
  6. tlc53

    Save worksheet as new macro-enabled Workbook

    Hi, I nearly have this working. I want sheet 12 (called Journal) to be copied and saved into a new macro-enabled workbook. It's getting stuck at the save part, saying "The following features cannot be saved in macro-free workbooks: VB project" Sub New_Journal() Dim wb As Workbook Set...
  7. B

    Creating a Word Document from an Excel spredsheet - formatting

    Hi, I presently have coding that will create the word document but I was wondering if it is possible to have coding that will help with the formatting of the Word document and the removal of bookmarks that are no used (no information in the corresponding cell in excel) the person fills...
  8. A

    Linear Interpolation in VBA

    Hey Everyone, I have been working on a function to linear interpolate some data- I got it to where it was giving values however they seem to be incorrect. I cant find an error in the equation so far so i am not sure where to fix things. I am also extremely new to VBA to bear with me on the...
  9. M

    [VBA] For next require vs for each

    Sub D_W() Dim X As Range, Y As Range For Each X In Range("B3:g3") Set Y = Range("J5:Q10").Find(X, LookIn:=xlValues, lookat:=xlWhole) If Not Y Is Nothing Then Y.Interior.ColorIndex = 6...
  10. M

    [VBA] Changing the range in the code FROM the spreadsheet

    Hi Folks. The code I am loading here is about to make calculation in the range ("B4:B18") "BUT" now I need to test the code in different ranges, example B4:B10, and after maybe B4:B22 and keep testing different ranges, so the "FAVOR, PLEASE" I need is type on ("I1") the start, and ("J1") the...
  11. M

    VBA A code within a loop

    Hi. I have a dynamic array at B3:G2650 and in A3:A20 the numbers from 1 to 17. The double-code uploaded here return the coefficient trend line results at J3:Q10 respectively in this frame and the other just highlight the first row of the array on the report. Until here everything is fine. The...
  12. M

    VBA-macro require a for next loop

    Hello folks. PLEASE, I have a macro, and I see some kind of loop concept to apply here but still thinking, so PLEASE somebody can give me a hand here. Sub Coe_fficient_trend_line() Range("P5").Formula = "=TRUNC(TREND(B3:B20))" Range("P6").Formula = "=TRUNC(TREND(C3:C20))" Range("P7").Formula...
  13. M

    Compile Error: cant find library or folder (DATE) in VBA

    I am running a macro in excel and i am having trouble with this error popping up and highlighting the Year(DATE) formula in my vba. i have searched and searched and have found things on how to replace the referrences but i dont know what reference to use to solve this issue. my program runs...
  14. tlc53

    IF Statement - refer to cells in a column

    Hi there, I am stumped as to how to construct this part of my IF formula Column F25 = 333,000.....G25 = 100,000 F26 = 100,000.....G26 = 333,000 F27 = 20,000.......G27 = 0 F28 ....................G28 F29 ....................G29 F30 ....................G30 This is my attempt but it's returning...
  15. tlc53

    VBA Code - Amend to include more references

    Hi there, Can anyone help me amend this VBA code to include more than one reference (if possible)? It currently refers only to D12 but I want to add another nine references - D68, D124, D180, D236, D292, D348, D404, D460 and D516. Thank you! Private Sub Worksheet_Change(ByVal Target As...
  16. M

    vba code to get all combinations and permutations IF they equal a cells

    Hello folks I have been trying to work around this code, I need some hands on here, Please. I am trying to generate all possible combinations and permutations possible from the set of numbers from 0 to 9 taken four at a time BUT display ONLY the one’s that match the conditions. The condition...
  17. tlc53

    Amend VBA code to refer to another sheet

    Hi, I have the below code which works fine if the target "No._Partners" is on the same sheet. However, in this case, "No._Partners" is based on another sheet. Can someone please help me change the first part of the code to refer to another sheet? I'm not quite sure how to do that.. Thanks...
  18. tlc53

    3x Checkboxes - Hide/Unhide sheet

    Hi there, I have 3x Form Check Boxes. If any of them are ticked, I would like sheet "Dairy" to be visible. If none of them are ticked, sheet "Dairy" should be hidden. I have linked the 3x check box references to cells P1, P2 and P3. In P4 I have the following formula...
  19. M

    VBA - Wrong results for a for next loop.

    Hello. This code do not give me any error return; but do not give me the right results either. I am trying to get the total of oranges sold only, on "C1". My data Fruit's names A, and quantity sold B and this is the code I tried. Sub Fr_Sd() Dim LastRow As Long...
  20. M

    vba Odd's and Even"s Array Count If

    Hello. I have a dynamic array B2:G2600 (6 columns), on Col "M" would like to see how many odd numbers there are in the rows, and on Col "N" the even numbers. So I workout first with the macro recorder, ("this is a short version as example, only"), Sub CORR_ECT()Range("H2").Select...

Some videos you may like

This Week's Hot Topics

Top