1. M

    Count when number moves to next full number

    10 years + Excel experience and consider myself to be 'advanced' but can't crack this one for the life of me! All help is greatly appreciated. See attached spreadsheet.... I have a cumulative tracker of 'packages sold' in row 54 and 55 (in green) In row 71 and 72 (in yellow), I would like to...
  2. L

    fill a range with numbers - vba

    Hi The code below is going to fill a range with random number but all the cells will get the same number. What I want, is to fill cells of that range with different random number. So I am thinking to put that line of code in a loop but how can I loop when user might enter any different range...
  3. K

    referencing cells from vba

    as shown in code below, i have defined two varible m and n and tried to use these variable for addressing a cell like range("cn") in place of range("c4") during first loop, but it shown run time error 1004 , cant i address a range in this way? Sub test() Dim diameter As Long Dim maxcap As Long...
  4. K

    Workbook_Change stopped working !

    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the user. The user is allowed access to 2 columns, 'Account Code' & 'VAT Rate'. Entry is restricted by Data Validation, the user selecting from...
  5. J

    Simplify Fraction UDF Error

    I have a Simply Fraction UDF which works fine whenever the numerator is less than the denominator, but fails other wise. For example for n = 50, d = 75 it outputs 2/3 but for n = 5 , d = 4 it is failing. Any reason? Private Function reduceFraction(n As Integer, d As Integer) Dim numL As...
  6. R

    VBA code to find colunn number of specific data

    Im using this code to color an specific row, from the starting column to the end column for a 4 weeks timeline graphic (not a grant chart) but since I'm using dates as headers the columns are not corresponding with the dates can you please help me to find the column number (StartColumn) based...
  7. B

    Error checking - Dim i as Integer

    Hello, I have the following code to check that each cell = 0. There are 12 cells, for each month of the year and I was trying to make it flag up which periods did not equal zero. However, this code will only show "Period1". Thanks in advance With Sheet22 Dim i As Integer i =...
  8. R

    Drag-down VBA function (many sheets) snag

    Hi all, I am experiencing the below issue with my VBA drag-down code across many sheets. It works for the first sheet then gets stuck - and help as to why would be appreciated. Here is the error message: Here is the code: Sub DragDown() Dim LR As Integer Dim...
  9. 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...
  10. L

    using sheet code name to call Activate function

    Hi I have workbook which has worksheet called hello or sheet1(hello) that is how it looks like in the vba project window. The code below does not work because of Workbooks(1).Sheet1.Activate but if I changed it to workbooks(1).worksheets("hello").Activate it will work! Why is that? thank...
  11. D

    Dividing to find an integer

    Hi, Is there a formula that would return an integer from a division calculation? For example: A1 will contain a varying non integer In A2 I would like to show the integer from dividing the value in A1 however, this division is limited by a set number. If A1 = 2.962 this number should then...
  12. T

    Passing arguments in a loop

    Instead of writing this: Option Explicit Sub Calling() Call Module1.Called(somearg:=1) Call Module1.Called(somearg:=2) Call Module1.Called(somearg:=3) End Sub Sub Called(ByRef somearg As Integer) somearg = somearg + 1 End Sub I would like to use...
  13. L

    copy a sheet from one workbook to another

    Hi I have 2 workbooks are open. Workbooks(1) and Workbooks(2). The code and the button to run the code are in workbooks(1). The code below does not work after I entered the following x= 1 y = 1 z =2 m = 1 Sub ws_copy_to_different_wb() Dim x As Integer Dim y As Integer Dim z As...
  14. F

    userform.Listbox don't show item

    Hi, I use the code below to create listboxs from adodb.recorset. If rs.recordcount are 0 I've nothing problem. If rs.recordcount are >1 I've nothing problem, the listbox show all record from recordset. But if rs.recordcout =1 the listbox don't show nothing. Somebody can help me to understand...
  15. C

    Help fixing 'Subscript out of range' error

    I'm trying to teach myself about arrays and can't seem to figure this one out. (many Google searches) I have the following code that I'm getting a 'subscript out of range' error: <subscript out="" of="" range="">Dim LastRow As Integer Dim MyArray As Variant Dim i As Integer With...
  16. M

    VBA to convert column range of date to long int

    Hi Folks, I have a script that copies ranges (columns) of data from one workbook to another. It copies a column of Dates in format MM/DD/YYYY and I'd like to convert that entire column to long integer. So here is my code that copies the columns over: Dim nmary As Variant, sh1 As Worksheet...
  17. S

    Copy range from one sheet to another, error 1004

    Hello, I'm wondering how to fix that, tried many things to declare ranges within sheets but still getting 438/1004 error and can't deal with that. I'm getting error at Paste line There is last-saved code, I tried many of things just like using "With worksheet" method. Usually I'm trying to...
  18. L

    printing all workbooks names in one shot

    Hi The code below is to print each workbook's name. If I have 5 then it will msgbox these 5 files names. I want to improve this code so it can print all the name in one shot (not 5 msgboxs or whatever the number of the files). Is that possible? Thank you so much. Sub wb_names() 'to find...
  19. I

    7: out of memory

    Hi When I click button to generate report there is the 7 out of memory error popup Private Sub btn_Click() On Error GoTo ErrCheck Dim strPeriod As String Dim intFY As Integer Dim strFMth As String Dim cnt As New ADODB.Connection Dim rst As ADODB.Recordset Dim...
  20. Z

    copy to range problem

    [code] Sub checkwood() Dim i As Integer Dim r2 As Integer Dim lastrow1 As Long Dim woodcoasters As Range lastrow1 = Cells(Rows.Count, 1).End(xlUp).Row For i = 2 To lastrow1 r2 = 2 If Cells(i, 3).Value = Cells(14, 3).Value Then Range("A" & i).Copy Range("j" &...

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