Tjyylli

What to do if authors don't respond to my serious concerns about their paper?

How would an AI self awareness kill switch work?

What is better: yes / no radio, or simple checkbox?

Do authors have to be politically correct in article-writing?

Does Windows 10's telemetry include sending *.doc files if Word crashed?

How is the Incom shipyard still in business?

Is there hidden data in this .blend file? Trying to minimize the file size

Can polymorphing monsters spam their ability to effectively give themselves a massive health pool?

Why avoid shared user accounts?

Are there any outlying considerations if I treat donning a shield as an object interaction during the first round of combat?

Can a person refuse a presidential pardon?

Slow moving projectiles from a hand-held weapon - how do they reach the target?

Can a hotel cancel a confirmed reservation?

View Angle Calculation

Number of FLOP (Floating Point Operations) for exponentiation

Why did Jodrell Bank assist the Soviet Union to collect data from their spacecraft in the mid 1960's?

How to remove trailing forward slash

If I delete my router's history can my ISP still provide it to my parents?

How to acknowledge an embarrassing job interview, now that I work directly with the interviewer?

Planet at the end of Solo: A Star Wars Story

Is it a fallacy if someone claims they need an explanation for every word of your argument to the point where they don't understand common terms?

What is the purpose of easy combat scenarios that don't need resource expenditure?

What is a good way to foreshadow that magic is actually very advanced technology?

Why do members of Congress in committee hearings ask witnesses the same question multiple times?

Change cell value then the will follow vba

Use Excel VBA If value in column a is not in column b then add value to column cHow to I simplify code that combines information and adds up values for certain columns?Excel Macro Hyperlink.Add Errorsvba if <all of these> = value thenExcel VBA function to update cells value += cell aboves valueCopy different ranges of a worksheet based on contents of a cell to different worksheetsReplace word in ms word with cell value in excel vbafill series with additional value vbadisplay current date base on cell value vbaLink send Email to Outlook and Subject is equal to cell value vba

0

I have a code that automatically fill series in column A.
My worksheet is protected with a password
and can only edit specific cells.

column A is the basis or the number of requested data.
Example:

Gee-2019-000

Gee-2019-001 ...

I just want the value in A2 which is Gee-2019-000 to be changed. If I enter Gee-2020-000 on A2 then the rest will follow.
Example:

Gee-2020-000

Gee-2020-001 ...

Note: only A2 is allowed to edit for the requested number.

Here's my code. I don't actually know how to do it, but I tried something just in case it will work, but it didn't.

Dim target As Excel.Range

    With target

        If .Address = Range("A2").Address Then

            .AutoFill Destination:=Range("A2:A1222"), Type:=xlFillDefault

        End If

    End With

microsoft-excel vba macros

share|improve this question

edited 18 mins ago

Supa Mega Ducky Momo da Waffle

146113

asked Feb 21 at 6:05

geegee

254

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Did the formula fill the column perhaps with the same value? Have you tried to manually use the drag fill on a blank sheet without password protection. The xlFillDefault can not find the pattern. Even after coercing the subsequent row to ...002, selecting both cells and drag filing the two together, there is no pattern, This is because there are numbers and letters mixed together in one cell. If the pattern is known, implement a loop to create it. Also, the range is 1221 rows (2-1222) but the pattern only allows for 1000 (000-999). This is another issue.
  
  – Ted D.
  Feb 21 at 7:54
  
  
  

  
  
  
  

add a comment | 

0

I have a code that automatically fill series in column A.
My worksheet is protected with a password
and can only edit specific cells.

column A is the basis or the number of requested data.
Example:

Gee-2019-000

Gee-2019-001 ...

I just want the value in A2 which is Gee-2019-000 to be changed. If I enter Gee-2020-000 on A2 then the rest will follow.
Example:

Gee-2020-000

Gee-2020-001 ...

Note: only A2 is allowed to edit for the requested number.

Here's my code. I don't actually know how to do it, but I tried something just in case it will work, but it didn't.

Dim target As Excel.Range

    With target

        If .Address = Range("A2").Address Then

            .AutoFill Destination:=Range("A2:A1222"), Type:=xlFillDefault

        End If

    End With

microsoft-excel vba macros

share|improve this question

edited 18 mins ago

Supa Mega Ducky Momo da Waffle

146113

asked Feb 21 at 6:05

geegee

254

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Did the formula fill the column perhaps with the same value? Have you tried to manually use the drag fill on a blank sheet without password protection. The xlFillDefault can not find the pattern. Even after coercing the subsequent row to ...002, selecting both cells and drag filing the two together, there is no pattern, This is because there are numbers and letters mixed together in one cell. If the pattern is known, implement a loop to create it. Also, the range is 1221 rows (2-1222) but the pattern only allows for 1000 (000-999). This is another issue.
  
  – Ted D.
  Feb 21 at 7:54
  
  
  

  
  
  
  

add a comment | 

I have a code that automatically fill series in column A.
My worksheet is protected with a password
and can only edit specific cells.

column A is the basis or the number of requested data.
Example:

Gee-2019-000

Gee-2019-001 ...

I just want the value in A2 which is Gee-2019-000 to be changed. If I enter Gee-2020-000 on A2 then the rest will follow.
Example:

Gee-2020-000

Gee-2020-001 ...

Note: only A2 is allowed to edit for the requested number.

Here's my code. I don't actually know how to do it, but I tried something just in case it will work, but it didn't.

Dim target As Excel.Range

    With target

        If .Address = Range("A2").Address Then

            .AutoFill Destination:=Range("A2:A1222"), Type:=xlFillDefault

        End If

    End With

microsoft-excel vba macros

share|improve this question

edited 18 mins ago

Supa Mega Ducky Momo da Waffle

146113

asked Feb 21 at 6:05

geegee

254

Gee-2019-000

Gee-2019-001 ...

Gee-2020-000

Gee-2020-001 ...

Dim target As Excel.Range

    With target

        If .Address = Range("A2").Address Then

            .AutoFill Destination:=Range("A2:A1222"), Type:=xlFillDefault

        End If

    End With

share|improve this question

edited 18 mins ago

Supa Mega Ducky Momo da Waffle

146113

asked Feb 21 at 6:05

geegee

254

share|improve this question

edited 18 mins ago

Supa Mega Ducky Momo da Waffle

146113

asked Feb 21 at 6:05

geegee

254

edited 18 mins ago

Supa Mega Ducky Momo da Waffle

146113

edited 18 mins ago

Supa Mega Ducky Momo da Waffle

146113

asked Feb 21 at 6:05

geegee

254

asked Feb 21 at 6:05

geegee

254

2 Answers
2

active

oldest

votes

0

Replace .AutoFill Destination:=Range("A2:A1222"), Type:=xlFillDefault with:

Dim r As Range

Dim prefixVal As String

Dim i As Integer



i = 1

prefixVal = Left(Range("A2"), InStrRev(Range("A2"),"-"))

For Each r In Range("A3:A1222")

    r.Value = prefixVal & Format(i, "000")

    i = i + 1

Next r

share|improve this answer

edited Feb 21 at 8:28

answered Feb 21 at 8:18

Ted D.Ted D.

2647

add a comment | 

0

I would like to suggest Macro will fill destination Cells with increment, considering the Source Value.

• 
  

  Copy & Paste this Macro as Standard Module.

  
  
  

  Sub IncremntValue()
  
  
  
  Dim code As String
  
  code = InputBox("Enter Code")
  
  
  
  Range("B1").Value = code
  
  Range("B2").Formula = "=LEFT(B1,9)&TEXT(ROW(A1),""000"")"
  
  Range("B2").Select
  
  Selection.AutoFill Destination:=Range("B2:B200"), Type:=xlFillDefault
  
  
  
  End Sub
  
  

  
  

You get result like this:

• I'm assuming the INPUT value is Gee-2019-000.

As soon you modify source value in B1 or Input any new code, you get this:

N.B.

• Use of INPUTBOX makes the code more competent & faster.


• The Formula I've used =LEFT(B1,9)&TEXT(ROW(A1),""000"")" is
  situational, which may vary if the Code
  composition in cell B1 changes.
  


• Adjust Cell reference in the Formula as needed.

Edited:

To automate Auto Fill event better you use commands wrapped with Worsheet Change Event, rather than a Simple Macro.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("$A$1")) Is Nothing Then





Range("A2").Formula = "=LEFT($A$1,9)&TEXT(ROW(A1),""000"")"

Range("A2").Select

Selection.AutoFill Destination:=Range("A2:A20"), Type:=xlFillDefault



End If



End Sub

share|improve this answer

edited Feb 22 at 5:35

answered Feb 21 at 8:14

Rajesh SRajesh S

3,9641523

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This is a good approach, however, I gleaned from the OP code that the solution was targeted for the Sheet's Worksheet_Change module. This would create a circular reference (as well as _Calculate). Perhaps just manually entering the formula into cell A3 and drag filling the remainder of the rows. Once and done. Any edits to A2 would be updated in the rest of the cells by their formulas. =LEFT(A2,9)&TEXT(ROW(A1),"000"). Row A1 is not a typo, it forces the numbering to begin from one in cell A3 and assumes A2 was zero.
  
  – Ted D.
  Feb 21 at 8:39
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @TedD.,, thanks for observation, it's fully a situational Macro and the need was to handle String (Code) rather than number, would much easier. I've tried the Macro on A11 onwards and it's working, using ROW(A1) helps to increment.
  
  – Rajesh S
  Feb 21 at 8:49
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  The fact that the macro provided in this answer would not automatically execute, but instead be manually run (situationally), was exactly my point. From the context of the attempted OP solution, it seemed the desired solution would run automatically. This answer is good because once the macro has been executed manually one time, all subsequent edits to A2 (in the OP context) will not require the use of the macro (as the formula is now populating A3:A1222).
  
  – Ted D.
  Feb 21 at 15:56
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Probably better to use an absolute address, $A$2, in the formula "=LEFT($A$2,9)$TEXT(ROW(A1),""000"")" so autofill does not create dependency on dependency on dependency ... (A4="Left(A3..." A5="Left(A4..." and so on)
  
  – Ted D.
  Feb 21 at 16:19
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @TedD.,, check the edited part now I've included another Macro with Worksheet Change Event ☺
  
  – Rajesh S
  Feb 22 at 5:37
  

  
  
  
  

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%2f1408043%2fchange-cell-value-then-the-will-follow-vba%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

Replace .AutoFill Destination:=Range("A2:A1222"), Type:=xlFillDefault with:

Dim r As Range

Dim prefixVal As String

Dim i As Integer



i = 1

prefixVal = Left(Range("A2"), InStrRev(Range("A2"),"-"))

For Each r In Range("A3:A1222")

    r.Value = prefixVal & Format(i, "000")

    i = i + 1

Next r

share|improve this answer

edited Feb 21 at 8:28

answered Feb 21 at 8:18

Ted D.Ted D.

2647

add a comment | 

0

Replace .AutoFill Destination:=Range("A2:A1222"), Type:=xlFillDefault with:

Dim r As Range

Dim prefixVal As String

Dim i As Integer



i = 1

prefixVal = Left(Range("A2"), InStrRev(Range("A2"),"-"))

For Each r In Range("A3:A1222")

    r.Value = prefixVal & Format(i, "000")

    i = i + 1

Next r

share|improve this answer

edited Feb 21 at 8:28

answered Feb 21 at 8:18

Ted D.Ted D.

2647

add a comment | 

Replace .AutoFill Destination:=Range("A2:A1222"), Type:=xlFillDefault with:

Dim r As Range

Dim prefixVal As String

Dim i As Integer



i = 1

prefixVal = Left(Range("A2"), InStrRev(Range("A2"),"-"))

For Each r In Range("A3:A1222")

    r.Value = prefixVal & Format(i, "000")

    i = i + 1

Next r

share|improve this answer

edited Feb 21 at 8:28

answered Feb 21 at 8:18

Ted D.Ted D.

2647

Dim r As Range

Dim prefixVal As String

Dim i As Integer



i = 1

prefixVal = Left(Range("A2"), InStrRev(Range("A2"),"-"))

For Each r In Range("A3:A1222")

    r.Value = prefixVal & Format(i, "000")

    i = i + 1

Next r

share|improve this answer

edited Feb 21 at 8:28

answered Feb 21 at 8:18

Ted D.Ted D.

2647

answered Feb 21 at 8:18

Ted D.Ted D.

2647

answered Feb 21 at 8:18

Ted D.Ted D.

2647

0

I would like to suggest Macro will fill destination Cells with increment, considering the Source Value.

• 
  

  Copy & Paste this Macro as Standard Module.

  
  
  

  Sub IncremntValue()
  
  
  
  Dim code As String
  
  code = InputBox("Enter Code")
  
  
  
  Range("B1").Value = code
  
  Range("B2").Formula = "=LEFT(B1,9)&TEXT(ROW(A1),""000"")"
  
  Range("B2").Select
  
  Selection.AutoFill Destination:=Range("B2:B200"), Type:=xlFillDefault
  
  
  
  End Sub
  
  

  
  

You get result like this:

• I'm assuming the INPUT value is Gee-2019-000.

As soon you modify source value in B1 or Input any new code, you get this:

N.B.

• Use of INPUTBOX makes the code more competent & faster.


• The Formula I've used =LEFT(B1,9)&TEXT(ROW(A1),""000"")" is
  situational, which may vary if the Code
  composition in cell B1 changes.
  


• Adjust Cell reference in the Formula as needed.

Edited:

To automate Auto Fill event better you use commands wrapped with Worsheet Change Event, rather than a Simple Macro.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("$A$1")) Is Nothing Then





Range("A2").Formula = "=LEFT($A$1,9)&TEXT(ROW(A1),""000"")"

Range("A2").Select

Selection.AutoFill Destination:=Range("A2:A20"), Type:=xlFillDefault



End If



End Sub

share|improve this answer

edited Feb 22 at 5:35

answered Feb 21 at 8:14

Rajesh SRajesh S

3,9641523

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This is a good approach, however, I gleaned from the OP code that the solution was targeted for the Sheet's Worksheet_Change module. This would create a circular reference (as well as _Calculate). Perhaps just manually entering the formula into cell A3 and drag filling the remainder of the rows. Once and done. Any edits to A2 would be updated in the rest of the cells by their formulas. =LEFT(A2,9)&TEXT(ROW(A1),"000"). Row A1 is not a typo, it forces the numbering to begin from one in cell A3 and assumes A2 was zero.
  
  – Ted D.
  Feb 21 at 8:39
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @TedD.,, thanks for observation, it's fully a situational Macro and the need was to handle String (Code) rather than number, would much easier. I've tried the Macro on A11 onwards and it's working, using ROW(A1) helps to increment.
  
  – Rajesh S
  Feb 21 at 8:49
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  The fact that the macro provided in this answer would not automatically execute, but instead be manually run (situationally), was exactly my point. From the context of the attempted OP solution, it seemed the desired solution would run automatically. This answer is good because once the macro has been executed manually one time, all subsequent edits to A2 (in the OP context) will not require the use of the macro (as the formula is now populating A3:A1222).
  
  – Ted D.
  Feb 21 at 15:56
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Probably better to use an absolute address, $A$2, in the formula "=LEFT($A$2,9)$TEXT(ROW(A1),""000"")" so autofill does not create dependency on dependency on dependency ... (A4="Left(A3..." A5="Left(A4..." and so on)
  
  – Ted D.
  Feb 21 at 16:19
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @TedD.,, check the edited part now I've included another Macro with Worksheet Change Event ☺
  
  – Rajesh S
  Feb 22 at 5:37
  

  
  
  
  

add a comment | 

0

I would like to suggest Macro will fill destination Cells with increment, considering the Source Value.

• 
  

  Copy & Paste this Macro as Standard Module.

  
  
  

  Sub IncremntValue()
  
  
  
  Dim code As String
  
  code = InputBox("Enter Code")
  
  
  
  Range("B1").Value = code
  
  Range("B2").Formula = "=LEFT(B1,9)&TEXT(ROW(A1),""000"")"
  
  Range("B2").Select
  
  Selection.AutoFill Destination:=Range("B2:B200"), Type:=xlFillDefault
  
  
  
  End Sub
  
  

  
  

You get result like this:

• I'm assuming the INPUT value is Gee-2019-000.

As soon you modify source value in B1 or Input any new code, you get this:

N.B.

• Use of INPUTBOX makes the code more competent & faster.


• The Formula I've used =LEFT(B1,9)&TEXT(ROW(A1),""000"")" is
  situational, which may vary if the Code
  composition in cell B1 changes.
  


• Adjust Cell reference in the Formula as needed.

Edited:

To automate Auto Fill event better you use commands wrapped with Worsheet Change Event, rather than a Simple Macro.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("$A$1")) Is Nothing Then





Range("A2").Formula = "=LEFT($A$1,9)&TEXT(ROW(A1),""000"")"

Range("A2").Select

Selection.AutoFill Destination:=Range("A2:A20"), Type:=xlFillDefault



End If



End Sub

share|improve this answer

edited Feb 22 at 5:35

answered Feb 21 at 8:14

Rajesh SRajesh S

3,9641523

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This is a good approach, however, I gleaned from the OP code that the solution was targeted for the Sheet's Worksheet_Change module. This would create a circular reference (as well as _Calculate). Perhaps just manually entering the formula into cell A3 and drag filling the remainder of the rows. Once and done. Any edits to A2 would be updated in the rest of the cells by their formulas. =LEFT(A2,9)&TEXT(ROW(A1),"000"). Row A1 is not a typo, it forces the numbering to begin from one in cell A3 and assumes A2 was zero.
  
  – Ted D.
  Feb 21 at 8:39
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @TedD.,, thanks for observation, it's fully a situational Macro and the need was to handle String (Code) rather than number, would much easier. I've tried the Macro on A11 onwards and it's working, using ROW(A1) helps to increment.
  
  – Rajesh S
  Feb 21 at 8:49
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  The fact that the macro provided in this answer would not automatically execute, but instead be manually run (situationally), was exactly my point. From the context of the attempted OP solution, it seemed the desired solution would run automatically. This answer is good because once the macro has been executed manually one time, all subsequent edits to A2 (in the OP context) will not require the use of the macro (as the formula is now populating A3:A1222).
  
  – Ted D.
  Feb 21 at 15:56
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Probably better to use an absolute address, $A$2, in the formula "=LEFT($A$2,9)$TEXT(ROW(A1),""000"")" so autofill does not create dependency on dependency on dependency ... (A4="Left(A3..." A5="Left(A4..." and so on)
  
  – Ted D.
  Feb 21 at 16:19
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @TedD.,, check the edited part now I've included another Macro with Worksheet Change Event ☺
  
  – Rajesh S
  Feb 22 at 5:37
  

  
  
  
  

add a comment | 

I would like to suggest Macro will fill destination Cells with increment, considering the Source Value.

• 
  

  Copy & Paste this Macro as Standard Module.

  
  
  

  Sub IncremntValue()
  
  
  
  Dim code As String
  
  code = InputBox("Enter Code")
  
  
  
  Range("B1").Value = code
  
  Range("B2").Formula = "=LEFT(B1,9)&TEXT(ROW(A1),""000"")"
  
  Range("B2").Select
  
  Selection.AutoFill Destination:=Range("B2:B200"), Type:=xlFillDefault
  
  
  
  End Sub
  
  

  
  

You get result like this:

• I'm assuming the INPUT value is Gee-2019-000.

As soon you modify source value in B1 or Input any new code, you get this:

N.B.

• Use of INPUTBOX makes the code more competent & faster.


• The Formula I've used =LEFT(B1,9)&TEXT(ROW(A1),""000"")" is
  situational, which may vary if the Code
  composition in cell B1 changes.
  


• Adjust Cell reference in the Formula as needed.

Edited:

To automate Auto Fill event better you use commands wrapped with Worsheet Change Event, rather than a Simple Macro.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("$A$1")) Is Nothing Then





Range("A2").Formula = "=LEFT($A$1,9)&TEXT(ROW(A1),""000"")"

Range("A2").Select

Selection.AutoFill Destination:=Range("A2:A20"), Type:=xlFillDefault



End If



End Sub

share|improve this answer

edited Feb 22 at 5:35

answered Feb 21 at 8:14

Rajesh SRajesh S

3,9641523

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("$A$1")) Is Nothing Then





Range("A2").Formula = "=LEFT($A$1,9)&TEXT(ROW(A1),""000"")"

Range("A2").Select

Selection.AutoFill Destination:=Range("A2:A20"), Type:=xlFillDefault



End If



End Sub

share|improve this answer

edited Feb 22 at 5:35

answered Feb 21 at 8:14

Rajesh SRajesh S

3,9641523

answered Feb 21 at 8:14

Rajesh SRajesh S

3,9641523

answered Feb 21 at 8:14

Rajesh SRajesh S

3,9641523