Search:

Type: Posts; User: Gary's Student; Keyword(s):

Page 1 of 6 1 2 3 4

Search: Search took 0.06 seconds.

  1. Re: Search for a cell in a column with the last value and copy it down to the same column in the next row

    This assumes that column D has stuff in it in all sheets:


    Sub dural()
    Dim s As Worksheet, r1 As Range
    For Each s In Sheets
    Set r1 =...
  2. Replies
    2
    Views
    228

    Re: sort by month and day

    First enter the following User Defined Function in a Standard Module:


    Public Function SortKey(r As Range) As Long
    Dim s As String
    s = r.Text
    arr = Split(s, "/")
    SortKey = 100...
  3. Replies
    3
    Views
    166

    Re: Auto arrange dates formula

    See my answer here:

    excel - List top n items in column, which have highest sum in second column - Stack Overflow
  4. Replies
    3
    Views
    166

    Re: Auto arrange dates formula

    Because dates are really numerical values, it is easy to sort them by formula. In C1 enter:

    =MIN(A:A)

    In C2 enter the Array Formula:

    =MIN(IF(A:A>C1,A:A))

    and copy down. This effectively...
  5. Replies
    3
    Views
    238

    Re: Rounding up or down relative value

    How about:

    =IF(B1>A1,ROUNDUP(AVERAGE(A1,B1),0),ROUNDDOWN(AVERAGE(A1,B1),0))
  6. Replies
    5
    Views
    276

    Re: Inserting Blank Cells

    Assume the data is in column A, run this short macro:


    Sub BlankInserter()
    Dim A As Range, i As Long, N As Long
    Dim j As Long

    Set A = Range("A:A")
    N = Cells(Rows.Count,...
  7. Re: Change column format to date (day / month / year) with VBA

    See if this meets your needs:


    Sub DateFixer()
    Dim r As Range, rng As Range
    Set rng = Intersect(Range("A:A"), ActiveSheet.UsedRange)
    For Each r In rng
    v = r.Text
    ...
  8. Re: Need Help! - desired number to increment does not increment

    In A1 enter:

    ="'\\L_QA_PC5\movt inspection report\movt data\2016\4R\0816\[" & 800+ROW() & "bungkai.xls]1a'!$D$21"

    and copy down.
  9. Replies
    2
    Views
    181

    Re: Text to column

    Use formulas rather than text to columns. With raw data in A1, in B1 enter:

    =LEFT(A1,FIND(" ",A1,1)-1)

    and in C1 enter:

    =MID(A1,FIND(" ",A1,1)+1,9999)
  10. Re: Substituting for sequential parameters

    Consider the following user defined function


    Public Function NoNumbers(inpt As String) As String
    Dim i As Long

    NoNumbers = ""
    For i = 1 To Len(inpt)
    If Not...
  11. Re: Get address for specific cell using Offset

    In B4 enter:

    =ADDRESS(4,3+(ROWS($1:1)-1)*2)

    and copy down.
  12. Re: Sum a trailing range that's dynamic to ignore blank cells

    Use a helper column.

    In B13 enter:


    =IF(A13="",0,1)

    in B14 enter:
  13. Re: how to convert letter to number and vice versa

    With 4 in cell A1, in B1 enter:


    =DATE(2000,A1,1) and format that cell as "mmmm"

    with April in cell A1, in B1 enter:


    =MONTH(DATEVALUE("1" & A1 & 1965))
  14. Replies
    3
    Views
    118

    Re: Converting strings

    This little macro assumes that the repetition factor is less than 10:


    Sub Splitter()
    Dim N As Long, i As Long, K As Long, j As Long
    N = Cells(Rows.Count, 1).End(xlUp).Row

    ...
  15. Re: Determine Factors of Number & Counting

    Place a value in cell A1. In B1 enter the array formula:


    =LARGE((ROUND($A$1/ROW(INDIRECT("$1:$"&$A$1)),0)=$A$1/ROW(INDIRECT("$1:$"&$A$1)))*ROW(INDIRECT("$1:$"&$A$1)),ROW(1:1))

    and copy down....
  16. Re: VBA to change tab name to a cell value on that tab

    Will each B6 contain a formula or a typed value ??
  17. Replies
    2
    Views
    275

    Re: Msgbox if #N/A error

    Here is a very simple example of code that either pastes the result or issues an error message:


    Sub sample()
    Dim v As Variant
    On Error GoTo kapture
    v =...
  18. Re: Is there any way to separate every five row in a different column in excel?

    See:

    Is there any way to separate every four row in a different column in excel? - Super User
  19. Replies
    4
    Views
    216

    Re: Sheet naming from the cell.

    With your names in Sheet1, run this short macro:


    Sub SheetMaker()
    Dim N As Long, i As Long, ary

    With Sheets("Sheet1")
    N = .Cells(Rows.Count, "A").End(xlUp).Row
    ...
  20. Replies
    7
    Views
    297

    Re: Need data in separate columns

    Do you want to discard the country (us) if it is present ??
  21. Replies
    2
    Views
    126

    Re: Time Format Conversion

    Try the following User Defined Function:


    Public Function JustMinutes(sIN As String) As Double
    ary = Split(sIN, ", ")
    JustMinutes = 0
    For Each a In ary
    bry = Split(a, " ")...
  22. Replies
    12
    Views
    684

    Re: Best Combination for the Price

    Help us to help you.........give more details.
  23. Re: Macro that Makes a list of all dupicates?

    This will place the list of duplicates in Sheet2 column F:


    Sub FindDups()
    Dim r1 As Range, r2 As Range, N As Long
    Dim K As Long, wf As WorksheetFunction

    Set wf =...
  24. Re: Need help with ISERROR, in multiple cell

    Try using IFERROR():


    =IFERROR(IF(AQ9="",((D9*S9)/($AV$6*($AO9*0.83))),IF($AQ9>0,(D9*S9)/($AV$6*$AQ9),"")),"")
  25. Replies
    2
    Views
    310

    Re: Static date and time

    Lets say the cell with the formula is A1 and we want the date/time stored in B1. Place the following event macro in the worksheet code area:


    Private Sub Worksheet_Calculate()
    If...
Results 1 to 25 of 150
Page 1 of 6 1 2 3 4