I am preparing to make a few alterations to a spreadsheet that another person built. I have NOT made any changes to it yet.
They put in a button that allows portions of the worksheet to print. I am now using Excel 2007. Their code ran fine in 2003.
The button had worked in Excel 2003- but now (without any changes) throws an error «Compile Error: Cant find project or library.»
TRIM is highlighted in the following code: (See below for the whole sub)
If Trim(Worksheets(«Measurements»).Cells(RowCounter, ColumnCounter).Value) <> «» Then
If RowCounter > PrintRowMarker Then
PrintRowMarker = RowCounter
End If
If ColumnCounter > PrintColumnMarker Then
PrintColumnMarker = ColumnCounter + 1
End If
End If
I have added in the following references into Excel via the big button in the upper left and the «Excel Options» screens:
Analysis Toolpak and Analysis Toolpak-VBA. The sheet «Measurements» is present.
1) Are these the right packages for the TRIM function?
2) Is there a different Add-in that is needed?
3) Is there an additional step I must use to actually add in these packages?
4) Is there something else I should do?
Thanks-
Loralee
P.S. Access 2003 finds (it’s version of) TRIM fine- so a version is on the machine. This spreadsheet if failing in the same way on 2 machines- both with Office 2007 installed.
Private Sub BtnPrint_Click()
Dim RowStarter As Integer ‘Where to start checking rows for content
Dim RowCounter As Integer ‘The current row being checked
Dim ColumnCounter As Integer ‘The current column being checked
Dim PrintRowMarker As Long ‘The last row with data
Dim PrintColumnMarker As Long ‘The last column with data
Dim NoMoreData As Boolean ‘Look for a blank record
Dim RangeValue As String ‘Define the range string value
Dim EndRangeValue As String
PrintColumnMarker = 0
PrintRowMarker = 0 ‘Initialize the row printing marker
RowStarter = 5 ‘Print Starting Point
For RowCounter = 2005 To RowStarter Step -1
For ColumnCounter = 50 To 2 Step -1
If Trim(Worksheets(«Measurements»).Cells(RowCounter, ColumnCounter).Value) <> «» Then
If RowCounter > PrintRowMarker Then
PrintRowMarker = RowCounter
End If
If ColumnCounter > PrintColumnMarker Then
PrintColumnMarker = ColumnCounter + 1
End If
End If
Next ColumnCounter
If PrintRowMarker > 0 Then
End If
Next RowCounter
EndRangeValue = IndexToString(PrintRowMarker, PrintColumnMarker)
RangeValue = «$A$1:» & EndRangeValue
Worksheets(«Measurements»).PageSetup.PrintArea = RangeValue
Worksheets(«Measurements»).PageSetup.PrintGridlines = True ‘Turn on gridlines
Worksheets(«Measurements»).PrintPreview ‘PRINT THAT PUPPY!
End Sub
I am getting an overflow error on my attempt to trim cells within my range. The line I get the error on is, C.Value = .Trim(C.Value) Perhaps this can be done without intersect? I’ve tried without it but it leads to mismatch error.
Dim masterWB As Workbook
Dim dailyWB As Workbook
Dim C As Range
Application.DisplayAlerts = False
'Set Current Workbook as Master
Set masterWB = Application.ThisWorkbook
'Set some Workbook as the one you are copying from
Set dailyWB = Workbooks.Open("excelguy.xlsm")
'Copy the Range from dailyWB and Paste it into the MasterWB
dailyWB.Sheets("Summary").Range("A1:BJ200").Copy masterWB.Sheets("Master Summary").Range("A1").Rows("1:1")
'formatting and paste as values
Workbooks("excelguy Master.xlsm").Activate
Worksheets("Master Summary").Select
'trim values
columns("A:BJ").Select
With Application.WorksheetFunction
For Each C In Intersect(columns("A:BJ"), ActiveSheet.UsedRange)
C.Value = .Trim(C.Value) 'Overflow Error
Next C
End With
Any help would be appreciated.
braX
11.5k5 gold badges20 silver badges34 bronze badges
asked Nov 26, 2018 at 16:50
5
- No need to
.Selector.Activateyour workbooks/sheets. You declared workbook variables so use them! UsedRangecan be unreliable. I recomend switching to a more standard last row calculation. Right now, the code is usingColumn Ato determine last row for all columns in your range, which in return determines the range you are going to loop through.- As stated by @dwirony, the
TRIMfunction can be called directly fromVBA.
Please see comment from @Tim Williams below to determine if the VBA version of Trim is acceptable
Option Explicit
Sub Test()
Dim masterWB As Workbook, dailyWB As Workbook
Dim C As Range, LRow As Long
Set masterWB = Application.ThisWorkbook
Set dailyWB = Workbooks.Open("excelguy.xlsm")
dailyWB.Sheets("Summary").Range("A1:BJ200").Copy masterWB.Sheets("Master Summary").Range("A1").Rows("1:1")
With masterWB.Sheets("Master Summary")
LRow = .Range("A" & .Rows.Count).End(xlUp).Row
For Each C In .Range("A2:BJ" & LRow)
C.Value = Trim(C)
'C.Value = Application.WorksheetFunction.Trim(C)
Next C
End With
End Sub
If you are just trimming the values, you could load your range into an array, modify the values into a new array, and then do a value transfer of your new trimmed array to a range
answered Nov 26, 2018 at 17:00
urdearboyurdearboy
14.4k5 gold badges28 silver badges58 bronze badges
6
Try this. To use Variant Array is faster.
Sub Test()
Dim masterWB As Workbook, dailyWB As Workbook
Dim C As Range, LRow As Long
Dim Ws As Worksheet
Dim rngDB As Range, vDB As Variant
Dim i As Long, j As Long
Set masterWB = ThisWorkbook
Set dailyWB = Workbooks.Open("excelguy.xlsm")
Set Ws = masterWB.Sheets("Master Summary")
dailyWB.Sheets("Summary").Range("A1:BJ200").Copy Ws.Range("A1")
Ws.Activate
With Ws
Set rngDB = .UsedRange
vDB = rngDB
For i = 1 To UBound(vDB, 1)
For j = 1 To UBound(vDB, 2)
vDB(i, j) = Trim(vDB(i, j))
Next j
Next i
rngDB = vDB
End With
End Sub
answered Nov 27, 2018 at 1:12
Dy.LeeDy.Lee
7,5271 gold badge12 silver badges14 bronze badges
I have this bit of VBA that I’ve used on many Excel workbooks without issues. On a particular workbook, I’m getting a Runtime Error ’13’: Type Mismatch error as soon as it gets to the Cell = Trim(Cell) part. What could be causing this? I’ve looked through the data, but can’t find anything out of the ordinary that would be messing it up.
Sub TrimHS()
Application.Cursor = xlWait
Application.ScreenUpdating = False
Dim LastRow As Integer
LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Range("A83:G" & LastRow).Select
Dim rng As Range, Cell As Range
Set rng = Selection
For Each Cell In rng
Cell = Trim(Cell)
Next Cell
Application.Cursor = xlDefault
End Sub
Vityata
42.7k8 gold badges55 silver badges100 bronze badges
asked Mar 12, 2018 at 15:36
You have an error on a particular workbook, thus the Trim() gives error 13, if you are trying to trim it. Make sure that you have no error before trimming:
For Each Cell In rng
If Not IsError(Cell) Then
Cell = Trim(Cell)
End If
Next Cell
To see what is wrong with the code, write debug.print Cell before the Trim line. It would start printing a lot at the console, the last line to print should look like this: Error 2007 or similar.
This will show the cell with the error in a MsgBox and its Worksheet:
For Each Cell In rng
If Not IsError(Cell) Then
Cell = Trim(Cell)
Else
MsgBox "Error on " & Cell.Address & " in " & Cell.Parent.Name
End If
Next Cell
answered Mar 12, 2018 at 16:12
VityataVityata
42.7k8 gold badges55 silver badges100 bronze badges
2
Cell is of type Excel.Range and Trim outputs of type String
I believe cell.value=trim(cell.value) is what you’ll need
answered Mar 12, 2018 at 15:44
Nathan_SavNathan_Sav
8,4662 gold badges13 silver badges20 bronze badges
4
-
#1
I am using Trim Function in VBA to remove all spaces in a string stored in a cell.
My piece of code is like this
Code:
Adr = Application.WorksheetFunction.Trim(Cells(2, Target.Column))Also I have tried this
Code:
Adr = Trim(Cells(2, Target.Column))But in both the cases I am getting the value into the variable Adr with the spaces as it is in the cell.
What could be the problem? I am breaking my head on this simple issue.
TIA & Regards
GNaga
Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
-
#2
Hi,
Application.WorksheetFunction.Trim will remove all leading, trailing & multiple internal spaces
Trim will remove all leading & trailing spaces
Application.Worksheetfunction.substitute(Cells(2, Target.Column),» «,»»)
will remove ALL spaces.
HTH
Alan
-
#3
Thank you Alan.
Your solution using substitute works fine.
One thing I could’nt get you. What is the meaning of multiple internal spaces? My string also have multiple spaces between words but not on the leading and trailing ends.
Thank you so much for your timely help.
Regards
GNaga
-
#4
Hi
If you had a string:
[space] A [space] [space] B [space]
Application.Worksheetfunction.Trim would convert it to
A [space] B
But Trim would convert it to
A [Space] [Space] B
HTh
Alan
|
Почему не всегда выполняется Len(Trim…..)? |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
У меня в книге была ошибка, результат события листа оставлял данные в том массиве, в который проводилось копирование! Теперь тот массив перед копированием очищаю и все в порядке!!! а ведь уже собиралась гневное письмо писать в майкрософт 