AUTO FILL ADJACENT CELL WITH SPECIFIED VALUE

[Sinner]

Hi,

I have an xl sheet in which I have 8 columns.

From row2, when I enter a value in column1 & column2, I want to auto
fill cell (same row) of column 3, column4 & column 7 with OK, CLEAR &
DONE.

A clear button will clear the sheet from row2 to end of sheet so that
the headers in row 1 are intact & sheet is ready for re-use.
Need a small vb code which will ease & avoid formulas & run in
background.


Thx.

 

[The Code Cage Team]

A simple if statement can suffice for your wording like:
=IF(AND(A1<>"",B1="Yes"),"Ok","") so this formula says if the cell A1 is
not blank and the cell B1 contains the word Yes then display the word
Ok.

Does this help?


--
The Code Cage Team

Regards,
The Code Cage Team
'The Code Cage' (http://www.thecodecage.com)

 

[Sinner]

A simple if statement can suffice for your wording like:
=IF(AND(A1<>"",B1="Yes"),"Ok","") so this formula says if the cell A1is
not blank and the cell B1 contains the word Yes then display the word
Ok.

Does this help?

--
The Code Cage Team

Regards,
The Code Cage Team
'The Code Cage' (http://www.thecodecage.com)

thanks.
Well yes but as I would go down the sheet, it would increase
calculation time & make it slow since formulas are involved.
I guess a code will definitely speed up the required.

Thx.

 

[The Code Cage Team]

Formulae won't make it slow unless you haev 1000's of them, anywa
here's some code you can play around with!


Code
-------------------
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column = 1 Or Target.Address = "$B$1" Then Exit Sub
If Not Intersect(Target, Range("B:B")) Is Nothing Then
If Target <> "" And Target.Offset(0, -1) = "" Then
MsgBox "You cannot leave " & Target.Offset(0, -1).Address & " empty!", vbOKOnly, "Missing Data"
Target.ClearContents
NXT:
Target.Offset(0, 1).ClearContents
Target.Offset(0, 2).ClearContents
Target.Offset(0, 5).ClearContents
Target.Offset(0, -1).Select
Exit Sub
ElseIf Target = "" Then
GoTo NXT
ElseIf Not Intersect(Target, Range("B:B")) Is Nothing Then
If Target <> "" Then
Target.Offset(0, 1).Value = "Ok"
Target.Offset(0, 2).Value = "Clear"
Target.Offset(0, 5).Value = "Done"
End If
End If
End If
End Su
-------------------

--
The Code Cage Tea

Regards,
The Code Cage Team
'The Code Cage' (http://www.thecodecage.com

 

[Sinner]

Formulae won't make it slow unless you haev 1000's of them, anyway
here's some code you can play around with!

Code:
--------------------
    Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Cells.Count > 1 Then Exit Sub
  If Target.Column = 1 Or Target.Address = "$B$1" Then Exit Sub
  If Not Intersect(Target, Range("B:B")) Is Nothing Then
  If Target <> "" And Target.Offset(0, -1) = "" Then
  MsgBox "You cannot leave " & Target.Offset(0, -1).Address & " empty!", vbOKOnly, "Missing Data"
  Target.ClearContents
  NXT:
  Target.Offset(0, 1).ClearContents
  Target.Offset(0, 2).ClearContents
  Target.Offset(0, 5).ClearContents
  Target.Offset(0, -1).Select
  Exit Sub
  ElseIf Target = "" Then
  GoTo NXT
  ElseIf Not Intersect(Target, Range("B:B")) Is Nothing Then
  If Target <> "" Then
  Target.Offset(0, 1).Value = "Ok"
  Target.Offset(0, 2).Value = "Clear"
  Target.Offset(0, 5).Value = "Done"
  End If
  End If
  End If
  End Sub
--------------------

--
The Code Cage Team

Regards,
The Code Cage Team
'The Code Cage' (http://www.thecodecage.com)

Hi,

In same code, how will I be able to import a specific column (in my
case it is column 6 of text file) to column 2 in xl sheet.
Text file is '|' delimited and first row has header. It should ignore
the header of text file & import column 6 data to column 2 in xl sheet
& rest autofill is already mentioned in the above code.

If all works fine, then the result would be like:
column 2 having data (previously we were enterting value, now we will
import from text file a specific column)
(same row) column 3, column4 & column 7 with OK, CLEAR & DONE (same as
in code)

Thx.