Posts by DANDERSON

Re: Sort Cell Data Speparated By "|"

Awesome! Learn something new everyday. Thank you for the information and direction. I can use this technique to do exactly what I want.

Thanks again.

I have a multiple cells with values similar to the following:
P442-C|P642-C|P342-C|P842-C

I need to reorder the value to read:
P842-C|P642-C|P442-C|P342-C

With VBA, could you give me some advice.
Thanks!

Re: Replace tool issue....need advice

Quote from Dave Hawley

BTW, If I understand you could use SpecialCells to replace decimals in Text only.

Great, and thanks for the heads up.

Re: Replace tool issue....need advice

Well, I found the LIKE function, and created a mass LooptyLoop inside of a Loop to accomplish my task.

Remember LIKE!

Thanks

Re: Replace tool issue....need advice

Quote from ByTheCringe2

Two questions:
1. Is an extra column allowed for the result? (If no, must be VBA.)
?

NO, I need the new string in a variable.

Quote from ByTheCringe2

2. Are there many different strings like "SPC" or just a few known ones?

Yes, could be "S1.20", or just "1.20 "

Thanks for checking

Hello,

Thanks for looking into this!

I have multiple strings in a selection set.

My goal is to replace the decimal point with null for a specific requirement.
The requirement is this: "#.## "(Meaning: NUmeric.NumericNumericSpace)

Example String #1: 2.5 kips <---no change
Example String #2: 0.6 %%C <---no change
Example String #3: W1.4 <---no change
Example String #4: 0.086 SQ IN <---no change
Example String #5: 6.2.2 <---no change
Example String #6: SPC1.20 <---This one should be changed to: SPC120
Example String #7: 1.2000 <---no change

Now the real complicated string(multiple decimal points found in the same string.)

Example String # 8: 2.5 kips with 0.6 %%C using W1.4 mesh with 0.086 SQ IN. See 6.2.2 on SPC1.20 for series 1.2000.

#8 should changed to: 2.5 kips with 0.6 %%C using W1.4 mesh with 0.086 SQ IN. See 6.2.2 on SPC120 for series 1.2000. <---Note:SPC120 change

SPC1.20 is the ony part of the string that meets "#.## "(Meaning: NUmeric.NumericNumericSpace)

What would be the best method?
I am very new to VBA, Thank you.

Re: Add new rows, defined by a cell

Helps immensely!! Thank very much. I learned a few things to.
The code was a perfect example, and I was able to extend the range for 3 additional columns of data I just added.
Thank you very much again. I have thousands of rows of data this will work on.

Take care,
Dan
:thanx:

A B C
B001 - B003 14 A
B004 6 A
B005 14 A
B006 4 A
B007 10 A
B008 - A
B009 ~ B012 8 A
B015 - A

For this example I have 3 colums of data.
in column A I have: B001 - B003
With this case, I want to create additional rows like this with same sharing data in column B & C:

needs to be:

B001 14 A
B002 14 A
B003 14 A

In this example:

column A I have: B009 ~ 1012

needs to be:

B009 8 A
B012 8 A

Notice the "-" means all numbers as in 1-3 = 1,2,3
Notice the "~" means all numbers as in 1~3 = 1,3

Hope this makes sense. I greatly appreciate it.

I am attaching the .csv file that gets generated. I renamed with .txt extension to upload.
Thank you again
Dan

Re: determine if ANY workbook is open before closing down

Thanks! That was the lead I needed. I was able to modify it for my needs. I greatly appreciate it, have a great day!
Dan

Basically, I have written a VBA app that calls upon Excel, and opens Excel, then opens a workbook, and then makes a few modifications, and then saves and closes that workbook.

What I cant seem to figure out is how to determine if the user has any other workbooks open before the VBA app attempts to shut down Excel.

I have found example for a specific workbook, but I will not know what workbook a user night have open. I just want a check tool.

Thanks for the guidance,
Dan

Re: Event watch range not working with paste

That was it! I did not understand the error message before. Thank you so much to NateO and Andy Pope!

This is exactly what I was overlooking. I am still very new to this VBA stuff, and I appreciate it greatly.

Re: Event watch range not working with paste

Hmm, I am attaching my spredsheet, maybe this will help determine the problem.

Thanks again for the help.

Select cells B24:D24, then try to copy them to E24. only E24 will change to red, F24 & G24 do not. I had to modifiy the code to resume next on error because the code would error out with the error code descibed in the previous message.

NOTE:Sheets("Check-Working Data").Visible = False

Re: Event watch range not working with paste

I am still having problems when I select multiple cells to paste into watched cells.

I will get an error when trying to paste the the copied cells. (See attachment jpg) "Unable to set the ColorIndex property of the Font class"

Here is the latest code I am using. Thanks for the input NateO.

Thanks for the help, I think i am almost there.

[vba]
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim myRng As Range, cl As Range, tstVal As String
If Intersect(Target, Me.Range("B28:BJ29")) Is Nothing Then _
Exit Sub
Me.Unprotect
Set myRng = Intersect(Target, Me.Range("B28:BJ29"))
Let tstVal = Worksheets("Check-Working Data").Range("a1").Value
Application.ScreenUpdating = False
For Each cl In myRng
With cl
If .Value = tstVal Then
.Font.ColorIndex = 5
.Interior.ColorIndex = xlColorIndexNone
.Interior.Pattern = xlPatternNone
Else
.Font.ColorIndex = 2
.Interior.ColorIndex = 3
.Interior.Pattern = xlSolid
Sheets("working data").Select
ActiveSheet.Unprotect
Sheets("working data").Range("A3:A6").Font.ColorIndex = 2
With Sheets("working data").Range("A3:A6").Interior
.ColorIndex = 3
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Sheets("working data").Select
End If
End With

Next
Application.ScreenUpdating = True
Me.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub
[/vba]

Re: Event watch range not working with paste

Thank you for the input. I really appreciate it. I am working with it now, and will post back.

Re: Event watch range not working with paste

What I am trying to do is watch cells B8:B13,F8:F11,F13:F15,B54:B57 for change. If a cell in range of B8:B13,F8:F11,F13:F15,B54:B57 changes value,(currently my code looks to another page for cell comparison) from user manipulation, Range("A3:A6") are then highlighted and color changed to RED.

Thanks again for any input.
I am still rather new to VBA.

Re: Event watch range not working with paste

Great! Thanks for the guidelines. I will use code tags in the future.

Re: Experts, Event watch range not working with paste

Here is similiar code I use in another spreadsheet, with the same problem. This is a bit nore direct, and less cumbersome. Can you expand upon the loop idea?

Thanks again!
Dan[vba]
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rngtest As Range
Set rngtest = Range("B24:BJ25")
If Not Application.Intersect(Target, rngtest) Is Nothing Then
ActiveSheet.Unprotect
With Target
If .Value = Worksheets("Check-Working Data").Range(.Address) Then
.Font.ColorIndex = 5
.Interior.ColorIndex = xlColorIndexNone
.Interior.Pattern = xlPatternNone
Else
.Font.ColorIndex = 2
.Interior.ColorIndex = 3
.Interior.Pattern = xlSolid
Range("A3:A6").Font.ColorIndex = 2
With Range("A3:A6").Interior
.ColorIndex = 3
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End If
End With
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End If
End Sub[/vba]

Problem:
The following code works great as long as a user enters data manually into (1) cell at a time. If data is copied from multiple cells, and copied to multiple watch cells, the code does not work.

Any ideas?

Thanks,
Dan[vba]Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rngtest As Range
Set rngtest = Range("B8:B13,F8:F11,F13:F15,B54:B57")
If Not Application.Intersect(Target, rngtest) Is Nothing Then
ActiveSheet.Unprotect
With Target
If .Value = Worksheets("Check-Output Charts").Range(.Address) Then
.Font.ColorIndex = 5
.Interior.ColorIndex = xlColorIndexNone
.Interior.Pattern = xlPatternNone
Else
.Font.ColorIndex = 2
.Interior.ColorIndex = 3
.Interior.Pattern = xlSolid
Sheets("working data").Select
ActiveSheet.Unprotect
Sheets("working data").Range("A3:A6").Font.ColorIndex = 2
With Sheets("working data").Range("A3:A6").Interior
.ColorIndex = 3
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Sheets("output charts").Select
End If
End With
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End If
End Sub[/vba]

Re: Hide Rows if value is 0,

Thank you very much. I am now just learning about events. I have learned so much from this group, and really apprecaiate your support, and willingnes to teach others.

Thanks again *thomach*!

Dan

Re: Hide Rows if value is 0,

You are so quick!
That gets me started in the right direction.

Here is what I have so far. It works, but may not be the best method.
I am open to any suggestions.

I want to watch Rows 1:159. If column A = 0, then hide row
If column A > 0 Then unhide

Thanks Again,
Dan

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

HideZeroRows

End Sub

Option Explicit

Sub HideZeroRows()
Dim CheckNum As Long, LastRow As Long

Application.ScreenUpdating = False
LastRow = 159

For CheckNum = LastRow To 10 Step -1
If Cells(CheckNum, 1).Value = 0 Then Cells(CheckNum, 1).EntireRow.Hidden = True
If Cells(CheckNum, 1).Value > 0 Then Cells(CheckNum, 1).EntireRow.Hidden = False
Next CheckNum
Application.ScreenUpdating = True
End Sub