Replace cell if value of cell offset
Solved/Closed
Related:
- Replace cell if value of cell offset
- Mouse pointer is offset - Guide
- If cell contains date then return value ✓ - Excel Forum
- Excel "IF" function w/ date in test cell ✓ - Excel Forum
- Excel formula to check if cell contains a date - Excel Forum
- If cell A1 has text then cell B2 has today's Date ✓ - Excel Forum
2 responses
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
May 10, 2010 at 11:42 PM
May 10, 2010 at 11:42 PM
Could you please zip up sample html and resulting excel file and upload on some shared site like https://authentification.site and post back here the link to allow better understanding of how it is now and how you foresee.
I actually looked around and pieced together the coding that I needed. I tried several to trim the cells and only found this that did the trick that I needed. As far as the replace if I had the coding right but was trying to run it on a sheet where the data not "trimmed" so it was not able to calculate correctly as it was recognizing the numbers as text. Here is the coding for anyone in a similar situation.
' This starts trim of all cells
Application.DisplayAlerts = True
Application.EnableEvents = True 'should be part of Change Event macro
If Application.Calculation = xlCalculationManual Then
MsgBox "Calculation was OFF will be turned ON upon completion"
End If
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
'Also Treat CHR 0160, as a space (CHR 032)
Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
cell.Value = Application.Trim(cell.Value)
Next cell
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
'This replaces blanks with %
For myRow = 1 To 140
If Cells(myRow, 2).Value = "" And Cells(myRow, 6).Value = 0 Then
Cells(myRow, 2).Value = 100
End If
If Cells(myRow, 2).Value = "" And Cells(myRow, 6).Value > 0 Then
Cells(myRow, 2).Value = 98.44
End If
Next myRow
' This starts trim of all cells
Application.DisplayAlerts = True
Application.EnableEvents = True 'should be part of Change Event macro
If Application.Calculation = xlCalculationManual Then
MsgBox "Calculation was OFF will be turned ON upon completion"
End If
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
'Also Treat CHR 0160, as a space (CHR 032)
Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
cell.Value = Application.Trim(cell.Value)
Next cell
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
'This replaces blanks with %
For myRow = 1 To 140
If Cells(myRow, 2).Value = "" And Cells(myRow, 6).Value = 0 Then
Cells(myRow, 2).Value = 100
End If
If Cells(myRow, 2).Value = "" And Cells(myRow, 6).Value > 0 Then
Cells(myRow, 2).Value = 98.44
End If
Next myRow
