Search:

Type: Posts; User: Rick Rothstein; Keyword(s):

Page 1 of 6 1

1. Thread: Macro for Message Pop-up

by Rick Rothstein
Replies
1
Views
35

Re: Macro for Message Pop-up

You would not do what you asked with macros, rather, you would do it with event code.

For the MessageBox when going into or leaving a worksheet, right click that worksheet's tab and select "View...
2. Thread: Join all row cells in a single cell by vertical bar

by Rick Rothstein
Replies
15
Views
272

Re: Join all row cells in a single cell by vertical bar

Here is a formula solution...

=SUBSTITUTE(TRIM(IF(A2="","",TEXT(A2,"00 "))&IF(B2="","",TEXT(B2,"00 "))&IF(C2="","",TEXT(C2,"00 "))&IF(D2="","",TEXT(D2,"00 "))&IF(E2="","",TEXT(E2,"00...
3. Thread: Join all row cells in a single cell by vertical bar

by Rick Rothstein
Replies
15
Views
272

Re: Join all row cells in a single cell by vertical bar

Here is a formula solution...

=SUBSTITUTE(TRIM(A2&" "&B2&" "&C2&" "&D2&" "&E2&" "&F2&" "&G2&" "&H2&" "&I2&" "&J2)," ","|")
4. Thread: Extract and arrange the numbers small to larger values

by Rick Rothstein
Replies
16
Views
276

Re: Extract and arrange the numbers small to larger values

Am I remembering correctly from past postings of yours... except for the zero values, aren't your numbers in each row of Columns E:N always in sorted numerical order from left to right? If so, this...
5. Thread: Problem with VBA Loop

by Rick Rothstein
Replies
12
Views
409

Re: Problem with VBA Loop

Try changing that line of code to this and see if it works correctly...

sumall = sumall + Val(alpha_1y(i, 1))

by Rick Rothstein
Replies
18
Views
474

Re: formatting a date

The key to the TEXT function (and cell formatting) is that in the pattern string certain characters have special meaning (for example, the "d" standing for the day number in a date as opposed to...

by Rick Rothstein
Replies
18
Views
474

Re: formatting a date

I think this formula will do what you want...

=UPPER(TEXT(D4+F4-SIGN(B5&1)*C5/24,"\B\/ddhhmm\Z mmm\/\/"))

by Rick Rothstein
Replies
18
Views
474

Re: formatting a date

If your formula is works for the OP, then I think this one will also...

=TEXT(SUBSTITUTE(A1,"-",""),"00000")
9. Thread: Change how date is converted.

by Rick Rothstein
Replies
5
Views
170

Re: Change how date is converted.

How would you type in the date October 17, 2019 into a cell using only numbers and slashes?
10. Thread: Change how date is converted.

by Rick Rothstein
Replies
5
Views
170

Re: Change how date is converted.

So your system's natural date order is d/m/y but you are getting dates with m/d/y date order and you want to preserve them, is that correct? If so, I do not think you will be able to do that with...
11. Thread: Adding a +1 to a Row when dragging across Columns

by Rick Rothstein
Replies
2
Views
78

Re: Adding a +1 to a Row when dragging across Columns

This formula should produce the displayed results that you want when dragged across...

=COLUMNS(\$B1:B1)
12. Thread: Numbering fixed amount of Rows

by Rick Rothstein
Replies
4
Views
65

Re: Numbering fixed amount of Rows

Actually, using Arthur's formula as a base, we can do the macro without using a loop...

Sub Deciles()
Range("B1:B62208") = [IF({1},CEILING(ROW(A1:A62208)/7776,1))]
Range("B62209:B77757") =...
13. Thread: Numbering fixed amount of Rows

by Rick Rothstein
Replies
4
Views
65

Re: Numbering fixed amount of Rows

Here is a macro that will fill your range with constants rather than formulas...

Sub Deciles()
Dim R As Long
For R = 1 To 69985 Step 7776
Cells(R, "B").Resize(7776).Value = Int((R +...
14. Thread: excel 2002 convert numbers to mathematical

by Rick Rothstein
Replies
30
Views
2,217

Re: excel 2002 convert numbers to mathematical

Are your "numbers" left justified? If so, they are text values. In order for that to be the case, I suspect you have some trailing non-breaking space attached to the numbers. Give this a try...

1)...

by Rick Rothstein
Replies
3
Views
149

Re: Adding Numbers with Text in Cell

Here is a way to do this without using Regular Expressions...

Dim V As Variant, VV As Variant
For Each V In NumOrRng
If VarType(V) < vbArray Then...
16. Thread: If value is 12 then fill range 1-12 etc..

by Rick Rothstein
Replies
4
Views
53

Re: If value is 12 then fill range 1-12 etc..

This macro should do what you want...

Sub EnterNumberSeries()
[C1].Resize([A1]) = Evaluate("ROW(1:" & [A1] & ")")
End Sub
17. Thread: making excel recognize it's 20XX not 19XX

by Rick Rothstein
Replies
12
Views
129

Re: making excel recognize it's 20XX not 19XX

No, if you entered a date and the code "corrected" it for the year, your colleague's computer would show the corrected year (2045); however, if your colleague put a date into the worksheet using her...
18. Thread: making excel recognize it's 20XX not 19XX

by Rick Rothstein
Replies
12
Views
129

Re: making excel recognize it's 20XX not 19XX

They certainly did bury it deep; however, the OP said in his opening post that...
19. Thread: making excel recognize it's 20XX not 19XX

by Rick Rothstein
Replies
12
Views
129

Re: making excel recognize it's 20XX not 19XX

Give this workbook event code a try (it will automatically make all date years be in the 2000's when they are entered into a cell in Column F or G on the indicated sheets)...

Private Sub...
20. Thread: making excel recognize it's 20XX not 19XX

by Rick Rothstein
Replies
12
Views
129

Re: making excel recognize it's 20XX not 19XX

In addition to Joe4's question above, is there a particular sheet and range that your dates can be in or are you wanting a general fix for any sheet in the workbook inside of any cell on the sheet?
21. Thread: Prevent first row to be deleted

by Rick Rothstein
Replies
2
Views
49

Re: Prevent first row to be deleted

Maybe this in place of what you posted...

With .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
.AutoFilter Field:=1, Criteria1:="xxxx"
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End...
22. Thread: Cell recognize positive or negative value then adds certain texts to the 2 possibilities

by Rick Rothstein
Replies
9
Views
117

Re: Cell recognize positive or negative value then adds certain texts to the 2 possibilities

Assuming your value (percentage) is in cell A1, give this formula a try...

=TEXT(A1,"""Overestimate"" 0%;""Underestimate"" 0%;""Spot On""")
23. Thread: Replacing an On Error with an If, regarding copying cells that may not exist after a filter

by Rick Rothstein
Replies
7
Views
85

Re: Replacing an On Error with an If, regarding copying cells that may not exist after a filter

If your filter is hiding every row in the range being examined, the you code will stop with a "No Cells Found" error. See what I wrote in Message #4 .
24. Thread: Replacing an On Error with an If, regarding copying cells that may not exist after a filter

by Rick Rothstein
Replies
7
Views
85

Re: Replacing an On Error with an If, regarding copying cells that may not exist after a filter

The On Error code lines in Norie's code do not persist and should not affect any other code. With that said, if you can guarantee that at least one row will be visible when you run the macro, you can...
25. Thread: VBA Code - Delete rows once column A no longer numbers

by Rick Rothstein
Replies
2
Views
47

Re: VBA Code - Delete rows once column A no longer numbers

Does this macro do what you want...

Sub DeleteRubbishRows()
Dim LastNumberRow As Long, LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
LastNumberRow =...
Results 1 to 25 of 150
Page 1 of 6 1