Tjyylli

Eww, those bytes are gross

Does a paladin have to announce that they're using Divine Smite before attacking?

What will happen if I transfer large sums of money into my bank account from a pre-paid debit card or gift card?

Updating Statistics: Estimated Number of Rows not equal to Actual for Index Scan. Why?

microtype error with lualatex: "attempt to call field warning a nil value"

Why do all the books in Game of Thrones library have their covers facing the back of the shelf?

What is a good reason for every spaceship to carry a weapon on board?

Best way to strengthen a wheel?

Prioritising polygons in QGIS

Does it take energy to move something in a circle?

Does diversity provide anything that meritocracy does not?

Why do neural networks need so many training examples to perform?

Is a new boolean field better than null reference when a value can be meaningfully absent?

Why do we have to make "peinlich" start with a capital letter and also end with -s in this sentence?

What is the wife of a henpecked husband called?

How much mayhem could I cause as a fish?

"Starve to death" Vs. "Starve to the point of death"

Which RAF squadrons and aircraft types took part in the bombing of Berlin on the 25th of August 1940?

How do you voice extended chords?

Count repetitions of an array

Is there a lava-breathing lizard creature (that could be worshipped by a cult) in 5e?

How to not let the Identify spell spoil everything?

Potential client has a problematic employee I can't work with

Why does 0.-5 evaluate to -5?

How to VBA update a cell value based on another cell

Excel 2010 VBA or formula to find value in dynamic column based off entry (vlookup like) but copy the value and paste the value into the cellAuto update Excel databsesum up cells based on cell indices (row and column NUMBERS)How do I return a value that is not in it's own row?EXCEL: Need formula that returns last time a value was >= current valueExcel VBA function to update cells value += cell aboves valueUsing VLOOKUP to retrieve data in separate sheet based on valueHow can I make a volatile Excel formula static, specifically a formula containing the TODAY function?Excel - Increment vlookup column index when dragging downFind corresponding column and row data based on data from another cell in Excel

0

I've looked all over stack exchange and have not seen exactly what I'm looking for. I have an extensive list of some items that need constant calibration at different dates and intervals, and what I'm trying to do is make a VBA/Macro so that I can update any one specific item's calibration date with out having to look at each individual entry or using =VLOOKUP(). so far I have nothing solid I can call a program yet, but what I had envisioned was in a cell write the item's ID#, in another cell the new calibration exp date and have a button next to them so that is searches for the ID#, moves one column to the right and replace the text on the cell with the new date. Simple enough to explain but given the fact that I just started learning about VBA four hours ago, it's proving to be a challenge. So far I've toyed with some code to no avail:

range("a1").Formula = "=CELL("address", INDEX(H:J, MATCH(L11,H:H, 0),2))"

ActiveCell.FormulaR1C1 = "=INDEX(R[-5]C[-9], 3, 2, )"

I will provide more information or context if anyone needs it!

microsoft-excel worksheet-function vba

share|improve this question

asked Jul 26 '17 at 20:00

WestWindsDemonWestWindsDemon

17119

bumped to the homepage by Community♦ 26 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

add a comment | 

0

I've looked all over stack exchange and have not seen exactly what I'm looking for. I have an extensive list of some items that need constant calibration at different dates and intervals, and what I'm trying to do is make a VBA/Macro so that I can update any one specific item's calibration date with out having to look at each individual entry or using =VLOOKUP(). so far I have nothing solid I can call a program yet, but what I had envisioned was in a cell write the item's ID#, in another cell the new calibration exp date and have a button next to them so that is searches for the ID#, moves one column to the right and replace the text on the cell with the new date. Simple enough to explain but given the fact that I just started learning about VBA four hours ago, it's proving to be a challenge. So far I've toyed with some code to no avail:

range("a1").Formula = "=CELL("address", INDEX(H:J, MATCH(L11,H:H, 0),2))"

ActiveCell.FormulaR1C1 = "=INDEX(R[-5]C[-9], 3, 2, )"

I will provide more information or context if anyone needs it!

microsoft-excel worksheet-function vba

share|improve this question

asked Jul 26 '17 at 20:00

WestWindsDemonWestWindsDemon

17119

bumped to the homepage by Community♦ 26 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

add a comment | 

I've looked all over stack exchange and have not seen exactly what I'm looking for. I have an extensive list of some items that need constant calibration at different dates and intervals, and what I'm trying to do is make a VBA/Macro so that I can update any one specific item's calibration date with out having to look at each individual entry or using =VLOOKUP(). so far I have nothing solid I can call a program yet, but what I had envisioned was in a cell write the item's ID#, in another cell the new calibration exp date and have a button next to them so that is searches for the ID#, moves one column to the right and replace the text on the cell with the new date. Simple enough to explain but given the fact that I just started learning about VBA four hours ago, it's proving to be a challenge. So far I've toyed with some code to no avail:

range("a1").Formula = "=CELL("address", INDEX(H:J, MATCH(L11,H:H, 0),2))"

ActiveCell.FormulaR1C1 = "=INDEX(R[-5]C[-9], 3, 2, )"

I will provide more information or context if anyone needs it!

microsoft-excel worksheet-function vba

share|improve this question

asked Jul 26 '17 at 20:00

WestWindsDemonWestWindsDemon

17119

share|improve this question

asked Jul 26 '17 at 20:00

WestWindsDemonWestWindsDemon

17119

share|improve this question

asked Jul 26 '17 at 20:00

WestWindsDemonWestWindsDemon

17119

asked Jul 26 '17 at 20:00

WestWindsDemonWestWindsDemon

17119

asked Jul 26 '17 at 20:00

WestWindsDemonWestWindsDemon

17119

2 Answers
2

active

oldest

votes

0

When using quotes inside a formula you should double up on them:

Range("A1").Formula = "=CELL(""address"", INDEX(H:J, MATCH(L11,H:H, 0),2))"

ActiveCell.FormulaR1C1 = "=INDEX(R[-5]C[-9], 3, 2, )"

share|improve this answer

answered Jul 26 '17 at 21:45

BruceWayneBruceWayne

1,8921721

add a comment | 

0

This will get you pretty close. You can adjust this to fit your data.
The commented out MsgBox commands can be deleted, but are handy for debugging.

Sub SetCalDate()



    'Set up variables

    Dim ID, RowNum As Integer

    Dim NewCalDate As Date

    Dim IDNums As Range



    'Initialize variables

    ID = Range("A1").Value           'Assumes the ID to Find is in A1,

    NewCalDate = Range("B1").Value   'and the NewCalDate is in B1.

    'MsgBox NewCalDate

    Set IDNums = Range("C1:C10")     'This is the range of ID's to be searched.

    'You might need something like Set IDNums = Worksheets("Sheet3").Range("C1:C10")

    'if the ID numbers are on a different sheet.



    'Find the ID in IDNums

    RowNum = Application.WorksheetFunction.Match(ID, IDNums, 0)

    'MsgBox RowNum

    Range("D" & RowNum) = NewCalDate    'Assumes the CalDates are in column D.



End Sub

If you want to assign the macro to a button, put the code in a new module first, then make a button and assign the SetCalDate() macro to it as explained on this help page. Good luck.

share|improve this answer

edited Jul 27 '17 at 12:37

answered Jul 26 '17 at 21:46

BandersnatchBandersnatch

3,3642413

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
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

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1234782%2fhow-to-vba-update-a-cell-value-based-on-another-cell%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

When using quotes inside a formula you should double up on them:

Range("A1").Formula = "=CELL(""address"", INDEX(H:J, MATCH(L11,H:H, 0),2))"

ActiveCell.FormulaR1C1 = "=INDEX(R[-5]C[-9], 3, 2, )"

share|improve this answer

answered Jul 26 '17 at 21:45

BruceWayneBruceWayne

1,8921721

add a comment | 

0

When using quotes inside a formula you should double up on them:

Range("A1").Formula = "=CELL(""address"", INDEX(H:J, MATCH(L11,H:H, 0),2))"

ActiveCell.FormulaR1C1 = "=INDEX(R[-5]C[-9], 3, 2, )"

share|improve this answer

answered Jul 26 '17 at 21:45

BruceWayneBruceWayne

1,8921721

add a comment | 

When using quotes inside a formula you should double up on them:

Range("A1").Formula = "=CELL(""address"", INDEX(H:J, MATCH(L11,H:H, 0),2))"

ActiveCell.FormulaR1C1 = "=INDEX(R[-5]C[-9], 3, 2, )"

share|improve this answer

answered Jul 26 '17 at 21:45

BruceWayneBruceWayne

1,8921721

Range("A1").Formula = "=CELL(""address"", INDEX(H:J, MATCH(L11,H:H, 0),2))"

ActiveCell.FormulaR1C1 = "=INDEX(R[-5]C[-9], 3, 2, )"

share|improve this answer

answered Jul 26 '17 at 21:45

BruceWayneBruceWayne

1,8921721

answered Jul 26 '17 at 21:45

BruceWayneBruceWayne

1,8921721

answered Jul 26 '17 at 21:45

BruceWayneBruceWayne

1,8921721

0

This will get you pretty close. You can adjust this to fit your data.
The commented out MsgBox commands can be deleted, but are handy for debugging.

Sub SetCalDate()



    'Set up variables

    Dim ID, RowNum As Integer

    Dim NewCalDate As Date

    Dim IDNums As Range



    'Initialize variables

    ID = Range("A1").Value           'Assumes the ID to Find is in A1,

    NewCalDate = Range("B1").Value   'and the NewCalDate is in B1.

    'MsgBox NewCalDate

    Set IDNums = Range("C1:C10")     'This is the range of ID's to be searched.

    'You might need something like Set IDNums = Worksheets("Sheet3").Range("C1:C10")

    'if the ID numbers are on a different sheet.



    'Find the ID in IDNums

    RowNum = Application.WorksheetFunction.Match(ID, IDNums, 0)

    'MsgBox RowNum

    Range("D" & RowNum) = NewCalDate    'Assumes the CalDates are in column D.



End Sub

If you want to assign the macro to a button, put the code in a new module first, then make a button and assign the SetCalDate() macro to it as explained on this help page. Good luck.

share|improve this answer

edited Jul 27 '17 at 12:37

answered Jul 26 '17 at 21:46

BandersnatchBandersnatch

3,3642413

add a comment | 

0

This will get you pretty close. You can adjust this to fit your data.
The commented out MsgBox commands can be deleted, but are handy for debugging.

Sub SetCalDate()



    'Set up variables

    Dim ID, RowNum As Integer

    Dim NewCalDate As Date

    Dim IDNums As Range



    'Initialize variables

    ID = Range("A1").Value           'Assumes the ID to Find is in A1,

    NewCalDate = Range("B1").Value   'and the NewCalDate is in B1.

    'MsgBox NewCalDate

    Set IDNums = Range("C1:C10")     'This is the range of ID's to be searched.

    'You might need something like Set IDNums = Worksheets("Sheet3").Range("C1:C10")

    'if the ID numbers are on a different sheet.



    'Find the ID in IDNums

    RowNum = Application.WorksheetFunction.Match(ID, IDNums, 0)

    'MsgBox RowNum

    Range("D" & RowNum) = NewCalDate    'Assumes the CalDates are in column D.



End Sub

If you want to assign the macro to a button, put the code in a new module first, then make a button and assign the SetCalDate() macro to it as explained on this help page. Good luck.

share|improve this answer

edited Jul 27 '17 at 12:37

answered Jul 26 '17 at 21:46

BandersnatchBandersnatch

3,3642413

add a comment | 

This will get you pretty close. You can adjust this to fit your data.
The commented out MsgBox commands can be deleted, but are handy for debugging.

Sub SetCalDate()



    'Set up variables

    Dim ID, RowNum As Integer

    Dim NewCalDate As Date

    Dim IDNums As Range



    'Initialize variables

    ID = Range("A1").Value           'Assumes the ID to Find is in A1,

    NewCalDate = Range("B1").Value   'and the NewCalDate is in B1.

    'MsgBox NewCalDate

    Set IDNums = Range("C1:C10")     'This is the range of ID's to be searched.

    'You might need something like Set IDNums = Worksheets("Sheet3").Range("C1:C10")

    'if the ID numbers are on a different sheet.



    'Find the ID in IDNums

    RowNum = Application.WorksheetFunction.Match(ID, IDNums, 0)

    'MsgBox RowNum

    Range("D" & RowNum) = NewCalDate    'Assumes the CalDates are in column D.



End Sub

If you want to assign the macro to a button, put the code in a new module first, then make a button and assign the SetCalDate() macro to it as explained on this help page. Good luck.

share|improve this answer

edited Jul 27 '17 at 12:37

answered Jul 26 '17 at 21:46

BandersnatchBandersnatch

3,3642413

Sub SetCalDate()



    'Set up variables

    Dim ID, RowNum As Integer

    Dim NewCalDate As Date

    Dim IDNums As Range



    'Initialize variables

    ID = Range("A1").Value           'Assumes the ID to Find is in A1,

    NewCalDate = Range("B1").Value   'and the NewCalDate is in B1.

    'MsgBox NewCalDate

    Set IDNums = Range("C1:C10")     'This is the range of ID's to be searched.

    'You might need something like Set IDNums = Worksheets("Sheet3").Range("C1:C10")

    'if the ID numbers are on a different sheet.



    'Find the ID in IDNums

    RowNum = Application.WorksheetFunction.Match(ID, IDNums, 0)

    'MsgBox RowNum

    Range("D" & RowNum) = NewCalDate    'Assumes the CalDates are in column D.



End Sub

share|improve this answer

edited Jul 27 '17 at 12:37

answered Jul 26 '17 at 21:46

BandersnatchBandersnatch

3,3642413

answered Jul 26 '17 at 21:46

BandersnatchBandersnatch

3,3642413

answered Jul 26 '17 at 21:46

BandersnatchBandersnatch

3,3642413