PDA

View Full Version : [SOLVED] Need help restucturing VBA Macro please!



estatefinds
11-14-2016, 01:37 PM
The current macro the firat code in the list the Corpsman0000 curently runs with
CLetter but nneds to run without as this worksheet will be with only numerical data.

so I select the last piece of data in the last row of Column
C and run the macro and it returns anything that is found after that selected data.
and places the those values that are found after that data selected in column D20.


Please help thank you!!!

p45cal
11-15-2016, 06:40 AM
a shot in the dark:
Sub corpsman000()
Dim SRow As Long, LRow As Long, DRow As Long, CLetter, i As Long
i = 20
CLetter = ActiveCell.Value
Columns(4).ClearContents
LRow = Cells(Rows.Count, 3).End(xlUp).Row
'If DRow = Cells(Cells(Rows.Count, 4).End(xlUp).Row, 4).Row < 20 Then
' DRow = 20
'Else
' DRow = Cells(Cells(Rows.Count, 4).End(xlUp).Row, 4).Row
'End If
DRow = Application.Max(20, Cells(Cells(Rows.Count, 4).End(xlUp).Row, 4).Row)
Do Until i = LRow
If Cells(i, 3).Value = CLetter Then
Cells(DRow, 4) = Cells(i + 1, 3).Value
DRow = DRow + 1
End If
i = i + 1
Loop
End Sub

estatefinds
11-15-2016, 08:10 AM
Ok it worked in that it shows the entire number and shows what comes after the last selected in the column C but its missing the listing of everything that is found after the seleceted number. so the number that is selected is 1460 and the number that come after this selected number is 3467, 1460,1478. other than that pretty good. if you can can you make it so it lists everything that comes after what ever is selected please

Thank you for working on this I appreciate it:)

p45cal
11-15-2016, 10:50 AM
Sub corpsman000()
Dim SRow As Long, LRow As Range, DRow As Long, i As Long
i = 20
Columns(4).ClearContents
Set LRow = Cells(Rows.Count, 3).End(xlUp)
DRow = Application.Max(20, Cells(Cells(Rows.Count, 4).End(xlUp).Row, 4).Row)
Set FirstFound = Range(Cells(i, 3), LRow).Find(what:=ActiveCell.Value, after:=LRow, LookIn:=xlValues, lookat:=xlWhole, searchformat:=False)
If Not FirstFound Is Nothing Then Range(FirstFound.Offset(1), LRow).Copy Cells(DRow, 4)
End Sub

p45cal
11-15-2016, 11:06 AM
Oh I get you now, the code in post #2 would have worked but not all your 1460s are numeric (only one of them is).
If you want to accept strings too (even if they look like numbers) then change the line to:
If Cells(i, 3).Value = CLetter Or CStr(Cells(i, 3).Value) = CLetter Then

estatefinds
11-15-2016, 11:19 AM
Hello, I ran the code and the only numbers that should be resulted are the 3467, 1460,1478. so its only suppossed to show the number that is found after the selected number for example 1460 will retunr the result of 3467 1460 and 1478 as these are the only ones found right after. if this can be corrected as it now list all numbers in bewtween. just need the ones found right after the selected one. thank you:)

estatefinds
11-15-2016, 11:26 AM
disregard last post # 6 it works I edited that line and it works!!!! Thank you very much!!!:) great job!!!