Solved: VBA macro - conditional column fill

[arunlgt]

Hello Everyone :

I have been wrestling with this since yesterday but gotten nowhere,was hoping someone could show me the light

I have this workbook (attached here) in which I have the following 2 requirements :

1)if the user enters a value(which is a date) in column A : the value of '2' is automatically filled in column C


2)

a- if the user enters a positive number (+) in column D (eg : +100) : the value of 'DEP' is automatically filled in column 'H'

b- if the user enters a positive number (-) in column D (eg : -50) : the value of 'WD' is automatically filled in column 'H'.

Again as pointers would be much appreciated.

Many thanks

Attachment 2764

Attachment 2764

[Bob Phillips]

Add this into the worksheet code module

[vba]

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit

Application.EnableEvents = False

If Target.Row > 1 Then

If Not Intersect(Target, Me.Columns(1)) Is Nothing Then

Target.Offset(0, 2).Value = 2

ElseIf Not Intersect(Target, Me.Columns(4)) Is Nothing Then

If Target.Value > 0 Then

Target.Offset(0, 4).Value = "DEP"
Else

Target.Offset(0, 4).Value = "WD"
End If
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub
[/vba]

[arunlgt]

XLD :

Thanks for the help and time.I put the code in a module and put the cide in sheet 1,but it does not seem to work.when i enter a date in column A : nothing shows up in column C,same case with D and H.If you are running this fine can you please attach the worksheet,I am probably not deploying your code correctly

Thanks again

[Bob Phillips]

Did you put it in the worksheet code module?

[arunlgt]

yes: insert module --> copied and pasted your code

went to sheet 1 --> added a few trade dates (colmn A) -> but nothing shows up in column C,same with the other columns

[Bob Phillips]

No, not Insert Module, that is a standarde code module, not a sheet code module.

In Excel on your target sheet, right-click the sheet tab, and select VIew Code. Add the code there.

[arunlgt]

XLD:

yeeesh,sorry my bad

your code works super.but just one last question:

when i copy and paste multiple values(say 10 rows)

column A : column C works perfectly fine ( '2' gets filled)

but in column D : H it does not work( it works only when I copy and paste single values)

[Bob Phillips]

[vba]


Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

On Error GoTo ws_exit

Application.EnableEvents = False

If Target.Row > 1 Then

If Not Intersect(Target, Me.Columns(1)) Is Nothing Then

For Each cell In Target

cell.Offset(0, 2).Value = 2
Next cell

ElseIf Not Intersect(Target, Me.Columns(4)) Is Nothing Then

For Each cell In Target

If cell.Value > 0 Then

cell.Offset(0, 4).Value = "DEP"
Else

cell.Offset(0, 4).Value = "WD"
End If
Next cell
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub
[/vba]

[arunlgt]

AWESOME

works perfectly,I posted this in 3 forums: and of the 4 answers i got:this one is by far the perfect one.


XLD : not to be greedy for your knowledge but a related question in the same workbook: how can I read a value in Sheet2 from row 1 : column C and column D ------> Sheet1 first empty row of column D where

sheet2(row 1 : column c ) ----> sheet 1 ( first empty row : column D)

sheet2(row1: column D ) --> sheet 1 (second empty row : column D)

I am trying to do this by having the user click a button in sheet2 to send it into sheet1

any pointers please?

Thanks again very very much!

[arunlgt]

please note sheet2(row 1 : column C :column D) are constant: it is the place from where i want values to be read and filled into sheet 1 column D

[Bob Phillips]

This code should do it

[vba]

NextRow = Worksheets("Sheet1").Range("C1").End(xlDown).Row + 1
Worksheets("Sheet2").Range("C11").Copy Worksheets("Sheet1").Cells(NextRow, "C")
[/vba]

[arunlgt]

XLD :

very very close but i think there is a small bug :

this is what i get :

sheet 2 (c1:d1) --> sheet 1 ( first empty cell of column C : first empty column of D)


this is what I need :

sheet 2 (c1:d1) --> sheet 1 ( first empty cell of column C : second empty column of C)

in other words :

sheet 2(c1 ) ---> Sheet 1 (first empty cell of column C)
sheet 2 (d1) --> Sheet 1 (second empty cell of column C)

[Bob Phillips]

Why would you want to copy empty cells across, that seems pointless?

[arunlgt]

XLD :



to help you understand what i am trying to do: here is the complete workbook attached:

Attachment 2768


please see columns C and D of sheet 2 and how they relate to Sheet 1 - column D


Many thank !

[Bob Phillips]

I am sorry, that helps me not at all. I am not clear as to what you want to do.

[Aussiebear]

AWESOME I posted this in 3 forums: and of the 4 answers i got:this one is by far the perfect one.

Arunlgt, please be aware that forum policy is that if you feel the need to post the same issue on other forums, you need to post a link here.

[arunlgt]

Hmm ok let me explain :

in Sheet 2 in column C2 and D2 if I have : C2 = +100 and D2 = -50,when the user clicks a button:

the values are shifted to Sheet1 Column D in the following manner:

Sheet1:first empty cell of column D = Sheet 2: C2=+100
Sheet 1 : second empty cell of column D = Sheet 2 : D2= -50

Sheet 2: Sheet 1:

c2 d2 user clicks button D1(first empty cell) +100

+100 -50 -------------------> D2(2nd empty cell) -50


Please note the values are ALWAYS being read from C2 and D2 ONLY.that is the values could change but the cell that they are being read from (C2 D2) remains the same.

Please let me know if it is still unclear.sorry for confusing you.

[arunlgt]

ok i got it it is done.

[VBA]Sub DoIt()

Dim liRow As Long

liRow = 1

' cycle through column D, find first blank cell
While Len(Sheets("Sheet2").Range("D" & liRow)) > 0

liRow = liRow + 1

Wend

' liRow = the first empty cell in Sheet 2 in Column D

' set that cell to C2 from sheet 1

Sheets("Sheet2").Range("D" & liRow).Value = Sheets("Sheet1").Range("C2").Value

liRow = liRow + 1

' cycle through column D, find next blank cell
While Len(Sheets("Sheet2").Range("D" & liRow)) > 0

liRow = liRow + 1

Wend

'set this cell to D2 from sheet 1
Sheets("Sheet2").Range("D" & liRow).Value = Sheets("Sheet1").Range("D2").Value



End Sub[/VBA]

[arunlgt]

http://stackoverflow.com/questions/2...al-column-fill

[Bob Phillips]

So you get an answer on Saturday which you say does what you want, then post the same question on StackOverflow the next day, which you then say is not a question, but an answer. What is the point?