Thank you every one i was able to work with what i was given i truly appreciate the time you guys took to answer my questions
Siemper Fi
Thanks
Posts by BadBunny
-
-
Good morning, the SSN column is only the last 4 digits of the SSN, so i need it to stay as a text format other wise if the last 4 of the SSN has a "0" in a general format it would be removed. example the number could be 0045 or 0450 or 0677 etc. in this cases it would remove all the zeros and keep the higher digits starting from 1 and above. again all this is on header15
Assignment group Catalog Task Request Created User status Epic Access User Transferring Effective Date User ID Requested for Last name First name Middle Initial DOB SSN -
I ended up using the array code
The SSN column is header15
that would need to stay as a 4 digit number only
Thanks for your help KjBox
-
Thank you all for the help
KjBox - i like this. all is working how i wanted to the only thing is that i need to keep the SSN column as a text format with the 4 last digits. for the reason that they are some that start with a zero or end with a zero.
Thanks
-
this is my current code:
Code
Display MoreSub SNREPORT() Dim RowCount As Integer, CurrentRow As Integer Dim ColumnCount As Integer Dim Userfullname As String Dim xFind As String Dim xRep As String Dim xR As Range Dim UCase As String Dim TextStrng As String Dim Result() As String Dim DisplayText As String Dim objWkbk As Object Dim objWksh As Object Dim intLastRow As Integer Dim objCell As Object Dim aryAffiliateNameCleanup As Variant aryAffiliateNameCleanup = ThisWorkbook.Worksheets("Hashtable").Range("AffiliateNameConversion") Set objWkbk = Application.ActiveWorkbook Set objWksh = objWkbk.Sheets(1) intLastRow = objWksh.Cells.SpecialCells(xlCellTypeLastCell).Row Application.ScreenUpdating = False For CurrentRow = 2 To intLastRow Set objCell = objWksh.Cells(CurrentRow, 10) objCell.Replace What:=" x", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False objCell.Replace What:=".", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False objCell.Replace What:=" NULL", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False Set objCell = objWksh.Cells(CurrentRow, 13) objCell.Replace What:="x", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False objCell.Replace What:=".", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False Set objCell = objWksh.Cells(CurrentRow, 24) If (objCell.Value = "") Or (IsEmpty(objCell.Value2) = True) Or (IsEmpty(objCell.Text) = True) Or (objCell.Value = "N/A - Not Applicable") Then objCell.Value = "Verify Title" End If Set objCell = objWksh.Cells(CurrentRow, 16) With Selection .NumberFormat = "General" .Value = .Value End With Set objCell = objWksh.Cells(CurrentRow, 25) With objCell .NumberFormat = "General" .Value = .Value End With Set objCell = objWksh.Cells(CurrentRow, 30) With objCell .NumberFormat = "General" .Value = .Value End With Set objCell = objWksh.Cells(CurrentRow, 30) With objCell .NumberFormat = "General" .Value = .Value End With Columns("AA:AA").Select Selection.Replace What:="Quincy Medical Group 1101 Maine Street", Replacement:="Quincy Medical Group", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False Selection.Replace What:="ROCK ISLAND TRINITY WEST HOSP", Replacement:="ROCK ISLAND TRINITY WEST HOSPITAL", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False Selection.Replace What:="ANAMOSA JONES REGIONAL MED HOS", Replacement:="ANAMOSA JONES REGIONAL MED HOSPITAL", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False Selection.Replace What:="FT DODGE TRINITY REGIONAL HOSP", Replacement:="FT DODGE TRINITY REGIONAL HOSPITAL", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False Columns("AB:AB").Select Selection.Replace What:="street", Replacement:="ST", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False Selection.Replace What:="road", Replacement:="RD", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False Selection.Replace What:="drive", Replacement:="DR", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False Selection.Replace What:="parkway", Replacement:="PKWY", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False Selection.Replace What:=".", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False Selection.Replace What:="avenue", Replacement:="AVE", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False Selection.Replace What:="suite", Replacement:="STE", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False Selection.Replace What:="north", Replacement:="N", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False Selection.Replace What:="south", Replacement:="S", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False Selection.Replace What:="west", Replacement:="W", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False Selection.Replace What:="east", Replacement:="E", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False Selection.Replace What:="court", Replacement:="CT", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False Next objWksh.Cells(1, 1).Select Application.ScreenUpdating = True End Sub -
I just tried that and yes it would convert the column to General but it would not convert the data. still under text format
-
I have tried that and still will not convert the data.
Set objCell = objWksh.Cells(CurrentRow, 16)
With Selection
.NumberFormat = "General"
.Value = .Value
End With
also tried
Set objCell = objWksh.Cells(CurrentRow, 16)
With objCell
.NumberFormat = "General"
.Value = .Value
End With
i tried multiple ways but the numbers do not change from text format to general format
here is an example data sheet i am using
Assignment group Catalog Task Request Created User status Last First Data convert to number 63683 63683 -
I am trying to change the text format from a text cell to a general format cell i currently running a FOR NEXT loop.
this is what i currently have but its not making any changes,
Set objCell = objWksh.Cells(CurrentRow, 16)
With objCell.Select
objCell.Replace.NumberFormat = "General"
objCell.Replace.Value = .Value
End With
any thoughts