Mixing
Trim cell values in rows and columns in different sheets VBA
-1
I would like to trim the cells that have spaces in them. But the macro I now have does not work.
Sub DoSomething()
'Declare Variables
Dim i, r, c, count, lastColData, LastRowData, lastRowInput, StartSearch As Long
Dim shtData, shtInput, shtInputI, shtInputII, shtInputIII, shtInputIV, shtInputV, shtInputVI, shtCopy As Worksheet
Dim Cell As Range
Set shtData = Sheets("PQFR")
Set shtInput = Sheets("INPUT")
Set shtInputI = Sheets("INPUTI")
Set shtInputII = Sheets("INPUTII")
Set shtInputIII = Sheets("INPUTIII")
Set shtInputIV = Sheets("INPUTIV")
Set shtInputV = Sheets("INPUTV")
Set shtInputVI = Sheets("INPUTVI")
Set shtCopy = Sheets("INPUTR")
lastColData = shtData.Cells(4, shtData.Columns.count).End(xlToLeft).Column
LastRowData = shtData.Cells(shtData.Rows.count, "A").End(xlUp).row
lastRowInput = shtInputI.Cells(shtInputI.Rows.count, "A").End(xlUp).row
'lastRowData = 10
'Remove spaces from these sheets:
shtInput.Cells(r, 32).Value = Trim(shtInput.Cell(r, 32).Value)
shtInputI.Cells(4, c).Value = Trim(shtInputI.Cells(4, c)).Value
shtInputII.Cells(4, c).Value = Trim(shtInputII.Cell(4, c)).Value
shtInputIII.Cells(4, c).Value = Trim(shtInputIII.Cell(4, c)).Value
shtInputIV.Cells(4, c).Value = Trim(shtInputIV.Cells(4, c)).Value
End Sub
excel vba excel-vba trim spaces
edited Nov 21 '18 at 10:31
Pᴇʜ
22.2k42750
asked Nov 21 '18 at 10:18
Daphn123Daphn123
174
add a comment |
-1
I would like to trim the cells that have spaces in them. But the macro I now have does not work.
Sub DoSomething()
'Declare Variables
Dim i, r, c, count, lastColData, LastRowData, lastRowInput, StartSearch As Long
Dim shtData, shtInput, shtInputI, shtInputII, shtInputIII, shtInputIV, shtInputV, shtInputVI, shtCopy As Worksheet
Dim Cell As Range
Set shtData = Sheets("PQFR")
Set shtInput = Sheets("INPUT")
Set shtInputI = Sheets("INPUTI")
Set shtInputII = Sheets("INPUTII")
Set shtInputIII = Sheets("INPUTIII")
Set shtInputIV = Sheets("INPUTIV")
Set shtInputV = Sheets("INPUTV")
Set shtInputVI = Sheets("INPUTVI")
Set shtCopy = Sheets("INPUTR")
lastColData = shtData.Cells(4, shtData.Columns.count).End(xlToLeft).Column
LastRowData = shtData.Cells(shtData.Rows.count, "A").End(xlUp).row
lastRowInput = shtInputI.Cells(shtInputI.Rows.count, "A").End(xlUp).row
'lastRowData = 10
'Remove spaces from these sheets:
shtInput.Cells(r, 32).Value = Trim(shtInput.Cell(r, 32).Value)
shtInputI.Cells(4, c).Value = Trim(shtInputI.Cells(4, c)).Value
shtInputII.Cells(4, c).Value = Trim(shtInputII.Cell(4, c)).Value
shtInputIII.Cells(4, c).Value = Trim(shtInputIII.Cell(4, c)).Value
shtInputIV.Cells(4, c).Value = Trim(shtInputIV.Cells(4, c)).Value
End Sub
excel vba excel-vba trim spaces
edited Nov 21 '18 at 10:31
Pᴇʜ
22.2k42750
asked Nov 21 '18 at 10:18
Daphn123Daphn123
174
1
Please not that "does not work" is no valid error description. Tell which error you get and where. Also note thatDim i, r, c, count, lastColData, LastRowData, lastRowInput, StartSearch As Longonly declares the last variable asLongbut all the others asVariantyou need to specify a type for every variable:Dim i As Long, r As Long, c As Long, count As Long, lastColData As Long, LastRowData As Long, lastRowInput As Long, StartSearch As Long
– Pᴇʜ
Nov 21 '18 at 10:34
2
Also note thatTrim(shtInputI.Cells(4, c)).Valuecannot work becauseTrimhas no.Valuethe parenthesis is set in the wrong place.
– Pᴇʜ
Nov 21 '18 at 10:36
The error I get is a 1004 Error during performance, due to the appliance or wrong defined object.This is shown as a yellow mark on the first Trim function.
– Daphn123
Nov 21 '18 at 10:40
of course because you did not define a value forrit isr = 0and row0does not exist. Also you should fix the issues I mentioned above and edit your code in the question.
– Pᴇʜ
Nov 21 '18 at 10:56
There's no value for c either so the same reason as @Pᴇʜ mentions
– Tom
Nov 21 '18 at 11:54
add a comment |
-1
-1
-1
I would like to trim the cells that have spaces in them. But the macro I now have does not work.
Sub DoSomething()
'Declare Variables
Dim i, r, c, count, lastColData, LastRowData, lastRowInput, StartSearch As Long
Dim shtData, shtInput, shtInputI, shtInputII, shtInputIII, shtInputIV, shtInputV, shtInputVI, shtCopy As Worksheet
Dim Cell As Range
Set shtData = Sheets("PQFR")
Set shtInput = Sheets("INPUT")
Set shtInputI = Sheets("INPUTI")
Set shtInputII = Sheets("INPUTII")
Set shtInputIII = Sheets("INPUTIII")
Set shtInputIV = Sheets("INPUTIV")
Set shtInputV = Sheets("INPUTV")
Set shtInputVI = Sheets("INPUTVI")
Set shtCopy = Sheets("INPUTR")
lastColData = shtData.Cells(4, shtData.Columns.count).End(xlToLeft).Column
LastRowData = shtData.Cells(shtData.Rows.count, "A").End(xlUp).row
lastRowInput = shtInputI.Cells(shtInputI.Rows.count, "A").End(xlUp).row
'lastRowData = 10
'Remove spaces from these sheets:
shtInput.Cells(r, 32).Value = Trim(shtInput.Cell(r, 32).Value)
shtInputI.Cells(4, c).Value = Trim(shtInputI.Cells(4, c)).Value
shtInputII.Cells(4, c).Value = Trim(shtInputII.Cell(4, c)).Value
shtInputIII.Cells(4, c).Value = Trim(shtInputIII.Cell(4, c)).Value
shtInputIV.Cells(4, c).Value = Trim(shtInputIV.Cells(4, c)).Value
End Sub
excel vba excel-vba trim spaces
edited Nov 21 '18 at 10:31
Pᴇʜ
22.2k42750
asked Nov 21 '18 at 10:18
Daphn123Daphn123
174
I would like to trim the cells that have spaces in them. But the macro I now have does not work.
Sub DoSomething()
'Declare Variables
Dim i, r, c, count, lastColData, LastRowData, lastRowInput, StartSearch As Long
Dim shtData, shtInput, shtInputI, shtInputII, shtInputIII, shtInputIV, shtInputV, shtInputVI, shtCopy As Worksheet
Dim Cell As Range
Set shtData = Sheets("PQFR")
Set shtInput = Sheets("INPUT")
Set shtInputI = Sheets("INPUTI")
Set shtInputII = Sheets("INPUTII")
Set shtInputIII = Sheets("INPUTIII")
Set shtInputIV = Sheets("INPUTIV")
Set shtInputV = Sheets("INPUTV")
Set shtInputVI = Sheets("INPUTVI")
Set shtCopy = Sheets("INPUTR")
lastColData = shtData.Cells(4, shtData.Columns.count).End(xlToLeft).Column
LastRowData = shtData.Cells(shtData.Rows.count, "A").End(xlUp).row
lastRowInput = shtInputI.Cells(shtInputI.Rows.count, "A").End(xlUp).row
'lastRowData = 10
'Remove spaces from these sheets:
shtInput.Cells(r, 32).Value = Trim(shtInput.Cell(r, 32).Value)
shtInputI.Cells(4, c).Value = Trim(shtInputI.Cells(4, c)).Value
shtInputII.Cells(4, c).Value = Trim(shtInputII.Cell(4, c)).Value
shtInputIII.Cells(4, c).Value = Trim(shtInputIII.Cell(4, c)).Value
shtInputIV.Cells(4, c).Value = Trim(shtInputIV.Cells(4, c)).Value
End Sub
excel vba excel-vba trim spaces
excel vba excel-vba trim spaces
edited Nov 21 '18 at 10:31
Pᴇʜ
22.2k42750
asked Nov 21 '18 at 10:18
Daphn123Daphn123
174
edited Nov 21 '18 at 10:31
Pᴇʜ
22.2k42750
asked Nov 21 '18 at 10:18
Daphn123Daphn123
174
edited Nov 21 '18 at 10:31
Pᴇʜ
22.2k42750
edited Nov 21 '18 at 10:31
Pᴇʜ
22.2k42750
edited Nov 21 '18 at 10:31
Pᴇʜ
22.2k42750
22.2k42750
asked Nov 21 '18 at 10:18
Daphn123Daphn123
174
asked Nov 21 '18 at 10:18
Daphn123Daphn123
174
asked Nov 21 '18 at 10:18
Daphn123Daphn123
174
174
1
Please not that "does not work" is no valid error description. Tell which error you get and where. Also note thatDim i, r, c, count, lastColData, LastRowData, lastRowInput, StartSearch As Longonly declares the last variable asLongbut all the others asVariantyou need to specify a type for every variable:Dim i As Long, r As Long, c As Long, count As Long, lastColData As Long, LastRowData As Long, lastRowInput As Long, StartSearch As Long
– Pᴇʜ
Nov 21 '18 at 10:34
2
Also note thatTrim(shtInputI.Cells(4, c)).Valuecannot work becauseTrimhas no.Valuethe parenthesis is set in the wrong place.
– Pᴇʜ
Nov 21 '18 at 10:36
The error I get is a 1004 Error during performance, due to the appliance or wrong defined object.This is shown as a yellow mark on the first Trim function.
– Daphn123
Nov 21 '18 at 10:40
of course because you did not define a value forrit isr = 0and row0does not exist. Also you should fix the issues I mentioned above and edit your code in the question.
– Pᴇʜ
Nov 21 '18 at 10:56
There's no value for c either so the same reason as @Pᴇʜ mentions
– Tom
Nov 21 '18 at 11:54
add a comment |
1
Please not that "does not work" is no valid error description. Tell which error you get and where. Also note thatDim i, r, c, count, lastColData, LastRowData, lastRowInput, StartSearch As Longonly declares the last variable asLongbut all the others asVariantyou need to specify a type for every variable:Dim i As Long, r As Long, c As Long, count As Long, lastColData As Long, LastRowData As Long, lastRowInput As Long, StartSearch As Long
– Pᴇʜ
Nov 21 '18 at 10:34
2
Also note thatTrim(shtInputI.Cells(4, c)).Valuecannot work becauseTrimhas no.Valuethe parenthesis is set in the wrong place.
– Pᴇʜ
Nov 21 '18 at 10:36
The error I get is a 1004 Error during performance, due to the appliance or wrong defined object.This is shown as a yellow mark on the first Trim function.
– Daphn123
Nov 21 '18 at 10:40
of course because you did not define a value forrit isr = 0and row0does not exist. Also you should fix the issues I mentioned above and edit your code in the question.
– Pᴇʜ
Nov 21 '18 at 10:56
There's no value for c either so the same reason as @Pᴇʜ mentions
– Tom
Nov 21 '18 at 11:54
1
1
Please not that "does not work" is no valid error description. Tell which error you get and where. Also note that
Dim i, r, c, count, lastColData, LastRowData, lastRowInput, StartSearch As Long only declares the last variable as Long but all the others as Variant you need to specify a type for every variable: Dim i As Long, r As Long, c As Long, count As Long, lastColData As Long, LastRowData As Long, lastRowInput As Long, StartSearch As Long– Pᴇʜ
Nov 21 '18 at 10:34
Please not that "does not work" is no valid error description. Tell which error you get and where. Also note that
Dim i, r, c, count, lastColData, LastRowData, lastRowInput, StartSearch As Long only declares the last variable as Long but all the others as Variant you need to specify a type for every variable: Dim i As Long, r As Long, c As Long, count As Long, lastColData As Long, LastRowData As Long, lastRowInput As Long, StartSearch As Long– Pᴇʜ
Nov 21 '18 at 10:34
2
2
Also note that
Trim(shtInputI.Cells(4, c)).Value cannot work because Trim has no .Value the parenthesis is set in the wrong place.– Pᴇʜ
Nov 21 '18 at 10:36
Also note that
Trim(shtInputI.Cells(4, c)).Value cannot work because Trim has no .Value the parenthesis is set in the wrong place.– Pᴇʜ
Nov 21 '18 at 10:36
The error I get is a 1004 Error during performance, due to the appliance or wrong defined object.This is shown as a yellow mark on the first Trim function.
– Daphn123
Nov 21 '18 at 10:40
The error I get is a 1004 Error during performance, due to the appliance or wrong defined object.This is shown as a yellow mark on the first Trim function.
– Daphn123
Nov 21 '18 at 10:40
of course because you did not define a value for
r it is r = 0 and row 0 does not exist. Also you should fix the issues I mentioned above and edit your code in the question.– Pᴇʜ
Nov 21 '18 at 10:56
of course because you did not define a value for
r it is r = 0 and row 0 does not exist. Also you should fix the issues I mentioned above and edit your code in the question.– Pᴇʜ
Nov 21 '18 at 10:56
There's no value for c either so the same reason as @Pᴇʜ mentions
– Tom
Nov 21 '18 at 11:54
There's no value for c either so the same reason as @Pᴇʜ mentions
– Tom
Nov 21 '18 at 11:54
add a comment |
1 Answer
1
active
oldest
votes
0
It's because you didn't make the loop. If you explain me better what you want, I can let the way you want.
Dim i, r, c, count, lastColData, LastRowData, lastRowInput, StartSearch As Long
For Each Worksheet In ActiveWorkbook.Worksheets
If Worksheet.Name = "PQFR" Or Worksheet.Name = "INPUT" Then 'please do for the sheets you want your command gonna`s be executed
Sheets(Worksheet.Name).Select
lastColData = Cells(4,Columns.count).End(xlToLeft).Column
LastRowData = Cells(Rows.count, 1).End(xlUp).Row
lastRowInput = Cells(Rows.count, 1).End(xlUp).Row
'using c=32 just for input
If Worksheet.Name = "INPUT" Then
'using r = 4 fosheets
For c = 32 To lastColData
For r = 1 To LastRowData
Cells(r, c) = Trim(Cells(r, c).Value)
Next r
Next c
Else
'using r = 4 for all the other sheets
For c = 1 To lastColData
For r = 4 To LastRowData
Cells(r, c) = Trim(Cells(r, c).Value)
Next r
Next c
End If
End If
Next Worksheet
edited Nov 21 '18 at 12:52
Pᴇʜ
22.2k42750
answered Nov 21 '18 at 12:00
LucasLucas
186
Thank you very much, works perfect!
– Daphn123
Nov 21 '18 at 12:16
Note thatDim i, r, c, count, lastColData, LastRowData, lastRowInput, StartSearch As Longonly declares the last variable asLongbut all the others asVariantyou need to specify a type for every variable:Dim i As Long, r As Long, c As Long, count As Long, lastColData As Long, LastRowData As Long, lastRowInput As Long, StartSearch As Long
– Pᴇʜ
Nov 21 '18 at 12:51
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53409847%2ftrim-cell-values-in-rows-and-columns-in-different-sheets-vba%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
It's because you didn't make the loop. If you explain me better what you want, I can let the way you want.
Dim i, r, c, count, lastColData, LastRowData, lastRowInput, StartSearch As Long
For Each Worksheet In ActiveWorkbook.Worksheets
If Worksheet.Name = "PQFR" Or Worksheet.Name = "INPUT" Then 'please do for the sheets you want your command gonna`s be executed
Sheets(Worksheet.Name).Select
lastColData = Cells(4,Columns.count).End(xlToLeft).Column
LastRowData = Cells(Rows.count, 1).End(xlUp).Row
lastRowInput = Cells(Rows.count, 1).End(xlUp).Row
'using c=32 just for input
If Worksheet.Name = "INPUT" Then
'using r = 4 fosheets
For c = 32 To lastColData
For r = 1 To LastRowData
Cells(r, c) = Trim(Cells(r, c).Value)
Next r
Next c
Else
'using r = 4 for all the other sheets
For c = 1 To lastColData
For r = 4 To LastRowData
Cells(r, c) = Trim(Cells(r, c).Value)
Next r
Next c
End If
End If
Next Worksheet
edited Nov 21 '18 at 12:52
Pᴇʜ
22.2k42750
answered Nov 21 '18 at 12:00
LucasLucas
186
Thank you very much, works perfect!
– Daphn123
Nov 21 '18 at 12:16
Note thatDim i, r, c, count, lastColData, LastRowData, lastRowInput, StartSearch As Longonly declares the last variable asLongbut all the others asVariantyou need to specify a type for every variable:Dim i As Long, r As Long, c As Long, count As Long, lastColData As Long, LastRowData As Long, lastRowInput As Long, StartSearch As Long
– Pᴇʜ
Nov 21 '18 at 12:51
add a comment |
0
It's because you didn't make the loop. If you explain me better what you want, I can let the way you want.
Dim i, r, c, count, lastColData, LastRowData, lastRowInput, StartSearch As Long
For Each Worksheet In ActiveWorkbook.Worksheets
If Worksheet.Name = "PQFR" Or Worksheet.Name = "INPUT" Then 'please do for the sheets you want your command gonna`s be executed
Sheets(Worksheet.Name).Select
lastColData = Cells(4,Columns.count).End(xlToLeft).Column
LastRowData = Cells(Rows.count, 1).End(xlUp).Row
lastRowInput = Cells(Rows.count, 1).End(xlUp).Row
'using c=32 just for input
If Worksheet.Name = "INPUT" Then
'using r = 4 fosheets
For c = 32 To lastColData
For r = 1 To LastRowData
Cells(r, c) = Trim(Cells(r, c).Value)
Next r
Next c
Else
'using r = 4 for all the other sheets
For c = 1 To lastColData
For r = 4 To LastRowData
Cells(r, c) = Trim(Cells(r, c).Value)
Next r
Next c
End If
End If
Next Worksheet
edited Nov 21 '18 at 12:52
Pᴇʜ
22.2k42750
answered Nov 21 '18 at 12:00
LucasLucas
186
Thank you very much, works perfect!
– Daphn123
Nov 21 '18 at 12:16
Note thatDim i, r, c, count, lastColData, LastRowData, lastRowInput, StartSearch As Longonly declares the last variable asLongbut all the others asVariantyou need to specify a type for every variable:Dim i As Long, r As Long, c As Long, count As Long, lastColData As Long, LastRowData As Long, lastRowInput As Long, StartSearch As Long
– Pᴇʜ
Nov 21 '18 at 12:51
add a comment |
0
0
0
It's because you didn't make the loop. If you explain me better what you want, I can let the way you want.
Dim i, r, c, count, lastColData, LastRowData, lastRowInput, StartSearch As Long
For Each Worksheet In ActiveWorkbook.Worksheets
If Worksheet.Name = "PQFR" Or Worksheet.Name = "INPUT" Then 'please do for the sheets you want your command gonna`s be executed
Sheets(Worksheet.Name).Select
lastColData = Cells(4,Columns.count).End(xlToLeft).Column
LastRowData = Cells(Rows.count, 1).End(xlUp).Row
lastRowInput = Cells(Rows.count, 1).End(xlUp).Row
'using c=32 just for input
If Worksheet.Name = "INPUT" Then
'using r = 4 fosheets
For c = 32 To lastColData
For r = 1 To LastRowData
Cells(r, c) = Trim(Cells(r, c).Value)
Next r
Next c
Else
'using r = 4 for all the other sheets
For c = 1 To lastColData
For r = 4 To LastRowData
Cells(r, c) = Trim(Cells(r, c).Value)
Next r
Next c
End If
End If
Next Worksheet
edited Nov 21 '18 at 12:52
Pᴇʜ
22.2k42750
answered Nov 21 '18 at 12:00
LucasLucas
186
It's because you didn't make the loop. If you explain me better what you want, I can let the way you want.
Dim i, r, c, count, lastColData, LastRowData, lastRowInput, StartSearch As Long
For Each Worksheet In ActiveWorkbook.Worksheets
If Worksheet.Name = "PQFR" Or Worksheet.Name = "INPUT" Then 'please do for the sheets you want your command gonna`s be executed
Sheets(Worksheet.Name).Select
lastColData = Cells(4,Columns.count).End(xlToLeft).Column
LastRowData = Cells(Rows.count, 1).End(xlUp).Row
lastRowInput = Cells(Rows.count, 1).End(xlUp).Row
'using c=32 just for input
If Worksheet.Name = "INPUT" Then
'using r = 4 fosheets
For c = 32 To lastColData
For r = 1 To LastRowData
Cells(r, c) = Trim(Cells(r, c).Value)
Next r
Next c
Else
'using r = 4 for all the other sheets
For c = 1 To lastColData
For r = 4 To LastRowData
Cells(r, c) = Trim(Cells(r, c).Value)
Next r
Next c
End If
End If
Next Worksheet
edited Nov 21 '18 at 12:52
Pᴇʜ
22.2k42750
answered Nov 21 '18 at 12:00
LucasLucas
186
edited Nov 21 '18 at 12:52
Pᴇʜ
22.2k42750
edited Nov 21 '18 at 12:52
Pᴇʜ
22.2k42750
edited Nov 21 '18 at 12:52
Pᴇʜ
22.2k42750
22.2k42750
answered Nov 21 '18 at 12:00
LucasLucas
186
answered Nov 21 '18 at 12:00
LucasLucas
186
answered Nov 21 '18 at 12:00
LucasLucas
186
186
Thank you very much, works perfect!
– Daphn123
Nov 21 '18 at 12:16
Note thatDim i, r, c, count, lastColData, LastRowData, lastRowInput, StartSearch As Longonly declares the last variable asLongbut all the others asVariantyou need to specify a type for every variable:Dim i As Long, r As Long, c As Long, count As Long, lastColData As Long, LastRowData As Long, lastRowInput As Long, StartSearch As Long
– Pᴇʜ
Nov 21 '18 at 12:51
add a comment |
Thank you very much, works perfect!
– Daphn123
Nov 21 '18 at 12:16
Note thatDim i, r, c, count, lastColData, LastRowData, lastRowInput, StartSearch As Longonly declares the last variable asLongbut all the others asVariantyou need to specify a type for every variable:Dim i As Long, r As Long, c As Long, count As Long, lastColData As Long, LastRowData As Long, lastRowInput As Long, StartSearch As Long
– Pᴇʜ
Nov 21 '18 at 12:51
Thank you very much, works perfect!
– Daphn123
Nov 21 '18 at 12:16
Thank you very much, works perfect!
– Daphn123
Nov 21 '18 at 12:16
Note that
Dim i, r, c, count, lastColData, LastRowData, lastRowInput, StartSearch As Long only declares the last variable as Long but all the others as Variant you need to specify a type for every variable: Dim i As Long, r As Long, c As Long, count As Long, lastColData As Long, LastRowData As Long, lastRowInput As Long, StartSearch As Long– Pᴇʜ
Nov 21 '18 at 12:51
Note that
Dim i, r, c, count, lastColData, LastRowData, lastRowInput, StartSearch As Long only declares the last variable as Long but all the others as Variant you need to specify a type for every variable: Dim i As Long, r As Long, c As Long, count As Long, lastColData As Long, LastRowData As Long, lastRowInput As Long, StartSearch As Long– Pᴇʜ
Nov 21 '18 at 12:51
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53409847%2ftrim-cell-values-in-rows-and-columns-in-different-sheets-vba%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Please not that "does not work" is no valid error description. Tell which error you get and where. Also note that
Dim i, r, c, count, lastColData, LastRowData, lastRowInput, StartSearch As Longonly declares the last variable asLongbut all the others asVariantyou need to specify a type for every variable:Dim i As Long, r As Long, c As Long, count As Long, lastColData As Long, LastRowData As Long, lastRowInput As Long, StartSearch As Long– Pᴇʜ
Nov 21 '18 at 10:34
2
Also note that
Trim(shtInputI.Cells(4, c)).Valuecannot work becauseTrimhas no.Valuethe parenthesis is set in the wrong place.– Pᴇʜ
Nov 21 '18 at 10:36
The error I get is a 1004 Error during performance, due to the appliance or wrong defined object.This is shown as a yellow mark on the first Trim function.
– Daphn123
Nov 21 '18 at 10:40
of course because you did not define a value for
rit isr = 0and row0does not exist. Also you should fix the issues I mentioned above and edit your code in the question.– Pᴇʜ
Nov 21 '18 at 10:56
There's no value for c either so the same reason as @Pᴇʜ mentions
– Tom
Nov 21 '18 at 11:54