.Formula method error: Object doesn't support this property or method
0
I tried to run the code below but I keep getting the error "object doesn't support this property or method". Is it because the .Formula refers to the integer i instead of the actual cell? Would really appreciate any feedback! Have been struggling with this for a while now :/ Thank you in advance!
Sub GetModelDescr()
Dim lastRow As Long
Dim i As Long
Dim rng As Range
Dim r As Range
Worksheets("Stock").Activate
With Worksheets("Stock")
lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
Set rng = .Range("B2:B" & lastRow)
For i = 1 To rng.Rows.Count
Set r = rng.Cells(i, 2)
.Formula "=TRIM(RC[6])&"" ""&MID(RC[-1],7,3)&"" ""&RIGHT(RC[-1],4)"
.Value2 = .Value2
Next i
End With
End Sub
excel vba methods formula
asked Nov 26 '18 at 9:19
Darck28Darck28
63
add a comment |
0
I tried to run the code below but I keep getting the error "object doesn't support this property or method". Is it because the .Formula refers to the integer i instead of the actual cell? Would really appreciate any feedback! Have been struggling with this for a while now :/ Thank you in advance!
Sub GetModelDescr()
Dim lastRow As Long
Dim i As Long
Dim rng As Range
Dim r As Range
Worksheets("Stock").Activate
With Worksheets("Stock")
lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
Set rng = .Range("B2:B" & lastRow)
For i = 1 To rng.Rows.Count
Set r = rng.Cells(i, 2)
.Formula "=TRIM(RC[6])&"" ""&MID(RC[-1],7,3)&"" ""&RIGHT(RC[-1],4)"
.Value2 = .Value2
Next i
End With
End Sub
excel vba methods formula
asked Nov 26 '18 at 9:19
Darck28Darck28
63
.Formula = .....Looks like you are applying the formula to the worksheet though, with yourwithblock. Why set a formula, then set it's value the next line?
– Nathan_Sav
Nov 26 '18 at 9:29
@Nathan_Sav I want to convert the formulas to values...but the code doesn't seem to be converting, any advice on how to convert?
– Darck28
Nov 26 '18 at 9:37
Have you tried with .FormulaR1C1? as you are giving R1C1 references.
– usmanhaq
Nov 26 '18 at 9:41
You need to change tor.formulafirst off, but you can just say something liker.value=trim(r.value) & " " & r.offset(0,-1).value.....
– Nathan_Sav
Nov 26 '18 at 9:42
Thanks for the tips and feedback! I found a faster way to do it by using With Range("C2:C" & lastRow) .Formula
– Darck28
Nov 27 '18 at 9:39
add a comment |
0
0
0
I tried to run the code below but I keep getting the error "object doesn't support this property or method". Is it because the .Formula refers to the integer i instead of the actual cell? Would really appreciate any feedback! Have been struggling with this for a while now :/ Thank you in advance!
Sub GetModelDescr()
Dim lastRow As Long
Dim i As Long
Dim rng As Range
Dim r As Range
Worksheets("Stock").Activate
With Worksheets("Stock")
lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
Set rng = .Range("B2:B" & lastRow)
For i = 1 To rng.Rows.Count
Set r = rng.Cells(i, 2)
.Formula "=TRIM(RC[6])&"" ""&MID(RC[-1],7,3)&"" ""&RIGHT(RC[-1],4)"
.Value2 = .Value2
Next i
End With
End Sub
excel vba methods formula
asked Nov 26 '18 at 9:19
Darck28Darck28
63
I tried to run the code below but I keep getting the error "object doesn't support this property or method". Is it because the .Formula refers to the integer i instead of the actual cell? Would really appreciate any feedback! Have been struggling with this for a while now :/ Thank you in advance!
Sub GetModelDescr()
Dim lastRow As Long
Dim i As Long
Dim rng As Range
Dim r As Range
Worksheets("Stock").Activate
With Worksheets("Stock")
lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
Set rng = .Range("B2:B" & lastRow)
For i = 1 To rng.Rows.Count
Set r = rng.Cells(i, 2)
.Formula "=TRIM(RC[6])&"" ""&MID(RC[-1],7,3)&"" ""&RIGHT(RC[-1],4)"
.Value2 = .Value2
Next i
End With
End Sub
excel vba methods formula
excel vba methods formula
asked Nov 26 '18 at 9:19
Darck28Darck28
63
asked Nov 26 '18 at 9:19
Darck28Darck28
63
asked Nov 26 '18 at 9:19
Darck28Darck28
63
asked Nov 26 '18 at 9:19
Darck28Darck28
63
asked Nov 26 '18 at 9:19
Darck28Darck28
63
63
.Formula = .....Looks like you are applying the formula to the worksheet though, with yourwithblock. Why set a formula, then set it's value the next line?
– Nathan_Sav
Nov 26 '18 at 9:29
@Nathan_Sav I want to convert the formulas to values...but the code doesn't seem to be converting, any advice on how to convert?
– Darck28
Nov 26 '18 at 9:37
Have you tried with .FormulaR1C1? as you are giving R1C1 references.
– usmanhaq
Nov 26 '18 at 9:41
You need to change tor.formulafirst off, but you can just say something liker.value=trim(r.value) & " " & r.offset(0,-1).value.....
– Nathan_Sav
Nov 26 '18 at 9:42
Thanks for the tips and feedback! I found a faster way to do it by using With Range("C2:C" & lastRow) .Formula
– Darck28
Nov 27 '18 at 9:39
add a comment |
.Formula = .....Looks like you are applying the formula to the worksheet though, with yourwithblock. Why set a formula, then set it's value the next line?
– Nathan_Sav
Nov 26 '18 at 9:29
@Nathan_Sav I want to convert the formulas to values...but the code doesn't seem to be converting, any advice on how to convert?
– Darck28
Nov 26 '18 at 9:37
Have you tried with .FormulaR1C1? as you are giving R1C1 references.
– usmanhaq
Nov 26 '18 at 9:41
You need to change tor.formulafirst off, but you can just say something liker.value=trim(r.value) & " " & r.offset(0,-1).value.....
– Nathan_Sav
Nov 26 '18 at 9:42
Thanks for the tips and feedback! I found a faster way to do it by using With Range("C2:C" & lastRow) .Formula
– Darck28
Nov 27 '18 at 9:39
.Formula = ..... Looks like you are applying the formula to the worksheet though, with your with block. Why set a formula, then set it's value the next line?– Nathan_Sav
Nov 26 '18 at 9:29
.Formula = ..... Looks like you are applying the formula to the worksheet though, with your with block. Why set a formula, then set it's value the next line?– Nathan_Sav
Nov 26 '18 at 9:29
@Nathan_Sav I want to convert the formulas to values...but the code doesn't seem to be converting, any advice on how to convert?
– Darck28
Nov 26 '18 at 9:37
@Nathan_Sav I want to convert the formulas to values...but the code doesn't seem to be converting, any advice on how to convert?
– Darck28
Nov 26 '18 at 9:37
Have you tried with .FormulaR1C1? as you are giving R1C1 references.
– usmanhaq
Nov 26 '18 at 9:41
Have you tried with .FormulaR1C1? as you are giving R1C1 references.
– usmanhaq
Nov 26 '18 at 9:41
You need to change to
r.formula first off, but you can just say something like r.value=trim(r.value) & " " & r.offset(0,-1).value.....– Nathan_Sav
Nov 26 '18 at 9:42
You need to change to
r.formula first off, but you can just say something like r.value=trim(r.value) & " " & r.offset(0,-1).value.....– Nathan_Sav
Nov 26 '18 at 9:42
Thanks for the tips and feedback! I found a faster way to do it by using With Range("C2:C" & lastRow) .Formula
– Darck28
Nov 27 '18 at 9:39
Thanks for the tips and feedback! I found a faster way to do it by using With Range("C2:C" & lastRow) .Formula
– Darck28
Nov 27 '18 at 9:39
add a comment |
0
active
oldest
votes
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%2f53477953%2fformula-method-error-object-doesnt-support-this-property-or-method%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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%2f53477953%2fformula-method-error-object-doesnt-support-this-property-or-method%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
.Formula = .....Looks like you are applying the formula to the worksheet though, with yourwithblock. Why set a formula, then set it's value the next line?– Nathan_Sav
Nov 26 '18 at 9:29
@Nathan_Sav I want to convert the formulas to values...but the code doesn't seem to be converting, any advice on how to convert?
– Darck28
Nov 26 '18 at 9:37
Have you tried with .FormulaR1C1? as you are giving R1C1 references.
– usmanhaq
Nov 26 '18 at 9:41
You need to change to
r.formulafirst off, but you can just say something liker.value=trim(r.value) & " " & r.offset(0,-1).value.....– Nathan_Sav
Nov 26 '18 at 9:42
Thanks for the tips and feedback! I found a faster way to do it by using With Range("C2:C" & lastRow) .Formula
– Darck28
Nov 27 '18 at 9:39