Select Case

[rhmkrmi]

Hi there,

How can I use Select Case in VBA for a cell where the value is calculated based on a formula?

In cell A1 in Sheet 2, I have =IF(ISBLANK('Sheet 1'!A1),"","Check") and I need the Select Case in VBA to populate cell N in Sheet 2 with "Check" if A1 in Sheet 2 gets "Check" and if A1 in Sheet 2 gets "" from the formula in it, then N in Sheet 2 should also be null.

Thanks.

 

[footoo]

With Sheets("Sheet 2")
      .N=.[A1].Value
End With

 

[rhmkrmi]

Thanks.

I added it into my code but it is not quite working:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range
Dim cell As Range
Dim rw As Long

Set rng = Intersect(Target, Range("A:A"))
If rng Is Nothing Then Exit Sub

With Sheets("Sheet2")
.N = .[A1].Value
End With

For Each cell In rng
rw = cell.Row
Select Case cell.Value
Case "Check"
Range(Cells(rw, "N"), Cells(rw, "N")) = "Check"
Case Else
Range(Cells(rw, "N"), Cells(rw, "N")) = ""
End Select
Next cell
End Sub

It finds a bug in " .N = .[A1].Value" and I also want range A and range N to be checked and populated.

If you could help with this, it would be great.

Thank you.

 

[footoo]

How are the values in Sheet1 column A changed. By formula or by manual input?

Instead of VBA, why not just put in Sheet2 column N a formula :
Either : =A1
Or : the same formula as column A

 

[rhmkrmi]

In Sheet 1 the data is entered manually.
This piece of data does not exist in Sheet 1 so I am trying to populate column N in Sheet 2 with this message if column A in Sheet 2 shows that there is data in column A in Sheet 1.

 

[footoo]

So you want column N (Sheet2) to be the same as column A (Sheet2) ?
If so, see post #4.

 

[rhmkrmi]

I want column N in Sheet 2 to be populated with "Check" if column A in Sheet 2 is populated with anything from the formula in it.

In my original post they are both "Check" but column A can have other formula in it too.

 

[footoo]

Maybe in column N (Sheet2) : =IF(A1="Check",A1,"")

If that doesn't do it, some sample data would help.

 

[rhmkrmi]

Yes it can work but I am trying not to use a formula in column N and have a macro do the job.
So, I need to populate column N cells in Sheet 2 with "Check" if the corresponding cell in column A in Sheet 1 is not blank.

Thank you.

 

[rhmkrmi]

I also got this code works for me but I have to run it manually!
I need to automate it so that when any cell in range A1:A100 is populated, the corresponding cell in column N automatically gets populated with "Check" but I do not know how to do it.

Sub populateB()
For Each cel In Range("A1:A100")
If cel.Value <> "" Then cel.Offset(0, 14).Value = "Check"
Next
End Sub