Creating a macro to count instances between spaces and return values

[AdventC]

Hi,

I'm having trouble creating a macro to count the number of instances something comes up and return a value in another cell depending on the number.
For example:

A 3
A 20
A 30

B 3
B 30
B 31

C 3
C 25

D 3
D 32

E -8
E 3
E 17

F -8
F 3

For this, I'd like the program to basically scan and see that if there are 3 instances (like A, B, and E) then to return nothing (blank value) and if it sees 2 instances, then to return the value "H" into a cell across the way from the first value in its series (IE: It finds D (say in cells A12 and A13) so I want "H" in cell block G12). Also, it the program reads a negative value (like -8) it just puts an "M" instead of the "H" into the result cell.

At first I thought I could just program it into the cells... I tried

<code>
=IF(SUM(F5)=0," ",IF(F5<0,"M",IF(INT(F5)=3,IF(COUNTA(INDIRECT("F5:F7"))=2,"H"," ")," ")))
</code>

But the problem I'm having is that I made the sheet to import data from text and insert spaces in between those A, B, C's etc...
The text file just has them all listed together, I have it put them into cells and ordered sorta. See below for what I mean.


Private Sub CommandButton1_Click()
Application.ScreenUpdating = False

Dim sh As Worksheet, flg As Boolean, c As Range, i As Long


For Each sh In Worksheets
If sh.Name Like "Sheet*" Then flg = True: Exit For
Next
If flg = False Then
Exit Sub
End If

'Taking Sample Names
Sheets("Sheet1").Select
Sheets("Sheet1").Range("A2:A300").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Copy
Sheets("X Data").Select
Range("A5").Select
ActiveSheet.Paste

'Taking the rest of the data
Sheets("Sheet1").Select
Sheets("Sheet1").Range("C2:D300").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Copy
Sheets("X Data").Select
Range("C5").Select
ActiveSheet.Paste

Let i = 5
Do Until Range("A" & i + 1) = ""
If Range("A" & i + 1) <> Range("A" & i) Then
Range("A" & i + 1).EntireRow.Insert
i = i + 1
End If
i = i + 1
Loop
Range("E5").Select


Application.ScreenUpdating = True

End Sub

The cells keep moving so im thinking I need a macro for this one. Any and all help would be appreciated and thank you all in advance!

 

[AdventC]

sorry, to correct my post "E" would just return a "M" result.

 

[AdventC]

can anyone help??

 

[AdventC]

figured it out. Next time ill go to another forum for help. If theres something wrong or lacking from the question then at least an admin should post or pm me saying to fix it. Other forums I get a response same day. Thanks for nothing.