Select Empty Cells Write Value

[OminousDark]

Hello, In my worksheet I have a column for roles of members - these are defined as "X", "Y", "Z" on my first worksheet ("X1"), although under the roles column in worksheet ("X2") it only lists fields for "Y" and "Z" as they are the more unique roles, and for "X" the fields are just blank. What I need is some VBA code to be able to write in "X" in all the blank fields under the "Roles" heading, but ONLY if it is meant to be apart of the participants data (I don't want "X" to be listed all the way down to row 1000000) possibly make it detect data to the left/right of the empty field?

Any help would be great, sorry if its hard to understand please just ask if you need some more information!

 

[Michael M]

Ok, which column contains the "roles" and which column requires an "X" and what sheet does this apply to ??

 

[OminousDark]

sorry - ok so, Roles is in column I, Column I needs "X" in the blank spots - and it applies to sheet "X2"

atm Column I has "Z" and "Y" and Blank - "X" needs to fill in those blank spots, but only to the last participants information - Although needs to be able to go further if more mpeople are added.

 

[Michael M]

OK, but what column on Sheet "x2" are we looking at to determine whether an "x" needs to be inserted in Col "I" of the Sheet "x1"

 

[Rick Rothstein]

sorry - ok so, Roles is in column I, Column I needs "X" in the blank spots - and it applies to sheet "X2"

atm Column I has "Z" and "Y" and Blank - "X" needs to fill in those blank spots, but only to the last participants information - Although needs to be able to go further if more mpeople are added.

Give this a try...

Sub PutXinBlankCells()
  On Error Resume Next
  Sheets("X2").Range("I1:I" & Sheets("X2").Columns("H:J").Find("*", , _
    xlValues, , xlRows, xlPrevious).Row).SpecialCells(xlBlanks) = "X"
  On Error GoTo 0
End Sub

 

[OminousDark]

That did not work sadly, Thanks anyways, I will try to explain it a bit better with different names for things now as I think my explanation was really bad.

Worksheet Name: Combined
Columns: A to J.
Column in which Role heading is located: I
Data under the Role heading: "D" and "S"
Column to the Left of Role (H): Postcode
Column to the Right of Role (J): Category
Rows: 3039

What I need it to do: Input "P" into all the BLANK spots in Column I as long as there is data to either the LEFT (H) or RIGHT (J) of the BLANK field. It should NOT ONLY go to I1:I3039 as MORE names / Information will be added in later down the track. (Thus the detect information to either side of it).

Row 1 is Headings.

Forget the second Worksheet, it is not of importance.

Sorry - Hope this information helps I greatly appreciate the help

- OminousDark

 

[Rick Rothstein]

That did not work sadly, Thanks anyways, I will try to explain it a bit better with different names for things now as I think my explanation was really bad.

Worksheet Name: Combined
Columns: A to J.
Column in which Role heading is located: I
Data under the Role heading: "D" and "S"
Column to the Left of Role (H): Postcode
Column to the Right of Role (J): Category
Rows: 3039

What I need it to do: Input "P" into all the BLANK spots in Column I as long as there is data to either the LEFT (H) or RIGHT (J) of the BLANK field. It should NOT ONLY go to I1:I3039 as MORE names / Information will be added in later down the track. (Thus the detect information to either side of it).

See if this code works for you...

Sub PutXinBlankCells()
  Dim LastRow As Long
  LastRow = Sheets("Combined").Columns("H:J").Find( _
            "*", , xlValues, , xlRows, xlPrevious).Row
  Sheets("Combined").Range("I2:I" & LastRow) = Evaluate(Replace( _
     "IF((Combined!I2:I#="""")*(LEN(Combined!H2:H#&Combined!J2:J#)>0)," & _
     """P"",IF(Combined!I2:I#="""","""",I2:I#))", "#", LastRow))
End Sub

 

[OminousDark]

Perfect thanks a ton