Solved: VBA macro - conditional column fill

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

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]

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

Did you put it in the worksheet code module?

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

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.

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)

[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]

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!

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

This code should do it

[vba]

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

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)

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

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 !

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

Quote:

---

Originally Posted by arunlgt

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.

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.

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]

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

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?

aussie bar wanted me to put a link to the site where i had out the same question
BTW i already closed the question yesterday there.

When you posted the question on the other site you inferred you were not getting anywhere, yet clearly XLD had been assisting you.

I'm not sure that your credabilty is being enhanced here.

i just copied and pasted my question:when i posted the question it was AT the same time in both places:lets be clear.
my last post at stackoverflow was my answer and i closed that thread

When you post a thread here ( and I'm assuming anywhere else for this intent) please allow some time for forum members to respond. Posting the same question "at the same time" on multiple forums is disrespectful to those members operating in the forums. You need to remember that all persons operating here do so on a voluntary basis and as such if you lose their respect for you then getting further assistance may well be near impossible.

I also cannot over emphasis the need to post the cross link. If you post on multiple forums with the same issue, we need to be able to see if a solution ( or a pathway to a solution) has been found before we provide assistance.

Further more the solution you were provided with here by XLD, was posted in the other forum and does not give XLD any credit for the work he did.

I urge you to give this advice strong consideration before you post here again.