Macro to Insert Character Depending on Value Help

[avd88]

Hello,

I am trying to write a code that when I press a button I get an X inserted in the relevant cell. If Z5 = 1 then put an X to AB5 if Z6 = 2 then put an X in AC6 (See Below). Eventually the Periods will change and AB through AE should be all filled out with Xs.

The range is dynamic so somethin like:

Dim nextrow As Integer

nextrow = WorksheetFunction.CountA(ActiveSheet.Range("T:T"))

I appreciate the help.

 

[Rick Rothstein]

See if assigning this macro to the button does what you want...

[table="width: 500"]
[tr]
	[td]Sub avd88()
  Dim cell As Range
  For Each cell In Columns("Z").SpecialCells(xlConstants)
    If Not cell.Value Like "*[!0-9]*" Then cell.Offset(, 1 + cell.Value) = "X"
  Next
End Sub[/td]
[/tr]
[/table]

 

[avd88]

Thanks for the reply Rick, unfortunately it did not work. So looking at the image I attached the rows on column Z with 2s and 1s (I go up to 5) should insert an X after I press a button on those same Rows on the columns where P1, P2, etc. are. So, 1s put an X on P1, 2s put an X on P2 and so on.

 

[Rick Rothstein]

Thanks for the reply Rick, unfortunately it did not work. So looking at the image I attached the rows on column Z with 2s and 1s (I go up to 5) should insert an X after I press a button on those same Rows on the columns where P1, P2, etc. are. So, 1s put an X on P1, 2s put an X on P2 and so on.

So are you saying you have one button per row? That seems inefficient. My code assumes you have one button which, when pressed, place the X in the appropriate column for all the cells in Column Z that have a number in them. If there is already an X in the cell, it will simply overwrite it with another X. This would make your interface easier as there would be only one button to press (and you could assign a shortcut key press to my macro and eliminate the button altogether). It would also allow you to put more than one number in Column Z at a time... calling my macro would then fill in all of the X's for them. Would that be an acceptable approach for you to use? If not, you are going to need a macro for every button on your worksheet (there is a way around that using Class modules, but you would probably not like it).

 

[avd88]

I only have one button, I think this image will explain better what I am trying to accomplish. After I click the DoneButton, this is what column AB to AF should look like. Eventually the numbers in column Z will change from 1-5 so the next month I click on that button the ones that have a 1 will now have a 2 and an X will be inserted into P2, so there will now be an X on P1 and an x on P2, and so on. The ones that started in Period 2 next month will have a blank on P1, and an X on P2 and P3....

 

[Rick Rothstein]

I only have one button, I think this image will explain better what I am trying to accomplish. After I click the DoneButton, this is what column AB to AF should look like. Eventually the numbers in column Z will change from 1-5 so the next month I click on that button the ones that have a 1 will now have a 2 and an X will be inserted into P2, so there will now be an X on P1 and an x on P2, and so on. The ones that started in Period 2 next month will have a blank on P1, and an X on P2 and P3....

Okay, I see one problem with my code based on the above explanation, but before I try and patch the code for it, I need you to tell me in what way did my code not work for you? Also, a second question... what is in Column Z, formulas or constants?

 

[avd88]

Ok I see the issue, I hard coded the 5 and your code does work, xlConstants is the problem as column Z are If statements.

 

[Rick Rothstein]

Ok I see the issue, I hard coded the 5 and your code does work, xlConstants is the problem as column Z are If statements.

Give this modified version of my macro a try...

[table="width: 500"]
[tr]
	[td]Sub avd88()
  Dim cell As Range
  For Each cell In Columns("Z").SpecialCells(xlFormulas, xlNumbers)
    cell.Offset(, 2).Resize(, 5).ClearContents
    cell.Offset(, 1 + cell.Value) = "X"
  Next
End Sub[/td]
[/tr]
[/table]

 

[avd88]

That works beautifully Rick, I just removed the

[COLOR=#333333][FONT=monospace]  cell.Offset(, 2).Resize(, 5).ClearContents[/FONT][/COLOR]

part of the code, as I need it to keep previous X's.

Thank you very much for your time and help.

 

[Rick Rothstein]

I just removed the

[COLOR=#333333][FONT=monospace]  cell.Offset(, 2).Resize(, 5).ClearContents[/FONT][/COLOR]

part of the code, as I need it to keep previous X's.

It was not at all clear that you wanted to do that given the picture you posted in Message #5 .