# Search:

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

Page 1 of 6 1

## Search: Search took 0.83 seconds.

by Rick Rothstein
Replies
26
Views
543

### Re: VBA Formula

I have modified the code I posted earlier and I believe it will now do what you want correctly...

Sub YesLast4Max()
Dim LR As Long, C As Long
For C = 2 To Cells(4,...
2. ## Thread: Autofill dates for specified year

by Rick Rothstein
Replies
11
Views
148

### Re: Autofill dates for specified year

Hmm! The code works perfectly for me. I guess the problem has to do with the fact that you, along with most of the rest of the world, uses the wrong date order when displaying...
3. ## Thread: Autofill dates for specified year

by Rick Rothstein
Replies
11
Views
148

### Re: Autofill dates for specified year

I missed that as I had run your code beforehand to see exactly what was being placed in the cells and doing that changed the cells to Date format.:banghead:

I can save my one-liner with the...
4. ## Thread: Autofill dates for specified year

by Rick Rothstein
Replies
11
Views
148

### Re: Autofill dates for specified year

Here is alternate one-liner that you can consider (assumes as Peter did that the starting date is in cell A1)...

Sub FillDates()
[A1].Resize(DateAdd("yyyy", 1, [A1]) - [A1]) = [A1+ROW(1:366)-1]...
5. ## Thread: Defining a name for more than one range

by Rick Rothstein
Replies
5
Views
109

### Re: Defining a name for more than one range

I am hardly an expert on Defined Names so others who are will hopefully chime in, but I could not find a way to create a disjointed Defined Name that is usable. The closest I could come to what you...
6. ## Thread: Defining a name for more than one range

by Rick Rothstein
Replies
5
Views
109

### Re: Defining a name for more than one range

With those defined names, you could also write that formula this way...

=SUM(Apple,Pie)
7. ## Thread: Reverse Hex Byte Pairs

by Rick Rothstein
Replies
29
Views
459

### Re: Reverse Hex Byte Pairs

Mike changed the name of his reversal function that the above function is calling (instead of ending in "Rev" here it ends in "Reverse"). Change what I highlighted in red to the name of the reversal...
8. ## Thread: Reverse Hex Byte Pairs

by Rick Rothstein
Replies
29
Views
459

### Re: Reverse Hex Byte Pairs

Here is another way to write this function...

Function Hex_Pairs_Rev(ByVal HexStr As String) As String
Dim N As Long, Arr() As String
Arr = Split(Trim(Format(HexStr, Application.Rept("@@ ",...
9. ## Thread: If cell is Empty!

by Rick Rothstein
Replies
5
Views
83

### Re: If cell is Empty!

If I am not mistaken, this non-looping event code should also work...

Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Evaluate("COUNTA(" & Sheets(1).Name & ":" &...
10. ## Thread: Shorten Formula =IF(D8=5,L8,0)+IF(D9=5,L9,0) repeat unto row 209 +IF(D209=5,L209,0)

by Rick Rothstein
Replies
2
Views
76

### Re: Shorten Formula =IF(D8=5,L8,0)+IF(D9=5,L9,0) repeat unto row 209 +IF(D209=5,L209,0)

Try using the SUMIF formula...

=SUMIF(D8:D209,5,L8:L209)
11. ## Thread: Defining a name for more than one range

by Rick Rothstein
Replies
5
Views
109

### Re: Defining a name for more than one range

Select the two ranges (select the first then hold down the CTRL key while selecting the second) and then type the Defined Name for this discontinuous range in the Name Box (located to the left of the...
12. ## Thread: Stripping text in Excel

by Rick Rothstein
Replies
20
Views
604

### Re: Stripping text in Excel

I think you will need to tighten up the above rule somewhat or else explain why message.[1] was not outputted from this sentence in your cell A1 example text...

"It is a coherent set of signs that...

by Rick Rothstein
Replies
26
Views
543

### Re: VBA Formula

Here is another macro that should work...

Sub YesLast4()
Dim LR As Long, C As Long
For C = 2 To Range("A1").End(xlToRight).Column
LR = Columns(C).Find("*", , xlValues, , xlRows,...
14. ## Thread: VBA: concatenate data

by Rick Rothstein
Replies
6
Views
119

### Re: VBA: concatenate data

Yikes! I completely missed that. :banghead: Thanks for pointing that out.

Simple fix though...

Since you indicated A1:A100 and B1:B100 all had data in them, this macro should work for you...
...
15. ## Thread: VBA: concatenate data

by Rick Rothstein
Replies
6
Views
119

### Re: VBA: concatenate data

Since you indicated A1:A100 and B1:B100 all had data in them, this macro should work for you...

Sub ConcatAandB()
[C1:C100] = [A1:A100&CHAR(10)&B1:B100]
End Sub

If the cells will not be...
16. ## Thread: Returning the 2nd, 3rd, 4th values from a cell based on a list

by Rick Rothstein
Replies
3
Views
137

### Re: Returning the 2nd, 3rd, 4th values from a cell based on a list

Does this formula do what you want where I have assumed your ingredients are delimited by a comma (shown in red below)...

=TRIM(MID(SUBSTITUTE(","&\$A2,",",REPT(" ",300)),COLUMNS(\$B:B)*300,300))
17. ## Thread: Displaying time as 1:00PM (etc) for a Label Value?

by Rick Rothstein
Replies
5
Views
146

### Re: Displaying time as 1:00PM (etc) for a Label Value?

In what way was it a "no-go"? Please describe happened...

Did you get an error? If so, what was it?

If not, what actually happened and what were you expecting to happen?

What was in the cell...

by Rick Rothstein
Replies
17
Views
204

The SUMPRODUCT function evaluates each cell of a range one cell at a time (you could also use SUM but then you would have to commit it with CTRL+SHIFT+ENTER) before performing the summation... adding...

by Rick Rothstein
Replies
17
Views
204

Does this formula work for you (using the original range you posted with your values remaining as Text)...

=SUMPRODUCT(C15:O15+0)

Format the cell you put this in using [h]:mm:ss
20. ## Thread: Two numbers to left of * and two numbers to right of *

by Rick Rothstein
Replies
4
Views
169

### Re: Two numbers to left of * and two numbers to right of *

Another way to consider...

Sub test()
Dim Response As String, Parts() As String
Response = "CEQ"
Parts = Split(ActiveSheet.Name, Response)
If UBound(Parts) Then
' Parts(0) will...
21. ## Thread: VBA to fill array with delimited data from text file

by Rick Rothstein
Replies
15
Views
321

### Re: VBA to fill array with delimited data from text file

Just reminding you both that this violates Forum Rule #4 . The reason for that rule is those of us who want to help can't because the information we need is only being shared with one person. The...
22. ## Thread: VBA to fill array with delimited data from text file

by Rick Rothstein
Replies
15
Views
321

### Re: VBA to fill array with delimited data from text file

I am unclear about the output you want. For the above, is it this in each cell of Column A...

CA
0
258
21122
0
0
0
0
23. ## Thread: VBA to find the day number of the year from today's date?

by Rick Rothstein
Replies
8
Views
218

### Re: VBA to find the day number of the year from today's date?

You can eliminate the concatenations by moving those numbers into the format pattern taking care to put a backslash in front of any 0's that are to remain constant within the pattern...

dn =...
24. ## Thread: VBA to find the day number of the year from today's date?

by Rick Rothstein
Replies
8
Views
218

### Re: VBA to find the day number of the year from today's date?

:confused: I thought you wanted a VBA solution... is that no longer the case?
25. ## Thread: Search in TXT file and return line to Excel

by Rick Rothstein
Replies
33
Views
747

### Re: Search in TXT file and return line to Excel

jphumani,

I have a question I am not too clear on... can the Identification Number appear more than once in any single text file?
Results 1 to 25 of 150
Page 1 of 6 1