# Search:

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

Page 1 of 6 1

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

by Gary's Student
Replies
7
Views
83

### 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. ## Thread: sort by month and day

by Gary's Student
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. ## Thread: Auto arrange dates formula

by Gary's Student
Replies
3
Views
166

### Re: Auto arrange dates formula

excel - List top n items in column, which have highest sum in second column - Stack Overflow
4. ## Thread: Auto arrange dates formula

by Gary's Student
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. ## Thread: Rounding up or down relative value

by Gary's Student
Replies
3
Views
238

### Re: Rounding up or down relative value

=IF(B1>A1,ROUNDUP(AVERAGE(A1,B1),0),ROUNDDOWN(AVERAGE(A1,B1),0))
6. ## Thread: Inserting Blank Cells

by Gary's Student
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. ## Thread: Change column format to date (day / month / year) with VBA

by Gary's Student
Replies
9
Views
16,093

### 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. ## Thread: Need Help! - desired number to increment does not increment

by Gary's Student
Replies
9
Views
283

### 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. ## Thread: Text to column

by Gary's Student
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. ## Thread: Substituting for sequential parameters

by Gary's Student
Replies
13
Views
297

### 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...

by Gary's Student
Replies
3
Views
136

### Re: Get address for specific cell using Offset

In B4 enter:

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

by Gary's Student
Replies
8
Views
234

### 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. ## Thread: how to convert letter to number and vice versa

by Gary's Student
Replies
7
Views
265

### 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))

by Gary's Student
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. ## Thread: Determine Factors of Number & Counting

by Gary's Student
Replies
5
Views
172

### 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. ## Thread: VBA to change tab name to a cell value on that tab

by Gary's Student
Replies
9
Views
3,707

### Re: VBA to change tab name to a cell value on that tab

Will each B6 contain a formula or a typed value ??
17. ## Thread: Msgbox if #N/A error

by Gary's Student
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. ## Thread: Is there any way to separate every five row in a different column in excel?

by Gary's Student
Replies
3
Views
255

### 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. ## Thread: Sheet naming from the cell.

by Gary's Student
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. ## Thread: Need data in separate columns

by Gary's Student
Replies
7
Views
297

### Re: Need data in separate columns

Do you want to discard the country (us) if it is present ??
21. ## Thread: Time Format Conversion

by Gary's Student
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. ## Thread: Best Combination for the Price

by Gary's Student
Replies
12
Views
684

23. ## Thread: Macro that Makes a list of all duplicates?

by Gary's Student
Replies
8
Views
274

### 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. ## Thread: Need help with ISERROR, in multiple cell

by Gary's Student
Replies
1
Views
174

### 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. ## Thread: Static date and time

by Gary's Student
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