Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 24

Thread: Solved: VBA macro - conditional column fill

  1. #1
    VBAX Regular
    Joined
    Feb 2010
    Posts
    33
    Location

    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

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,147
    Location
    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]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Regular
    Joined
    Feb 2010
    Posts
    33
    Location
    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

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,147
    Location
    Did you put it in the worksheet code module?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    VBAX Regular
    Joined
    Feb 2010
    Posts
    33
    Location
    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

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,147
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    VBAX Regular
    Joined
    Feb 2010
    Posts
    33
    Location
    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)

  8. #8
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,147
    Location
    [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]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  9. #9
    VBAX Regular
    Joined
    Feb 2010
    Posts
    33
    Location
    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!

  10. #10
    VBAX Regular
    Joined
    Feb 2010
    Posts
    33
    Location
    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

  11. #11
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,147
    Location
    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]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  12. #12
    VBAX Regular
    Joined
    Feb 2010
    Posts
    33
    Location
    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)

  13. #13
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,147
    Location
    Why would you want to copy empty cells across, that seems pointless?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  14. #14
    VBAX Regular
    Joined
    Feb 2010
    Posts
    33
    Location
    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 !
    Last edited by arunlgt; 02-13-2010 at 11:30 PM.

  15. #15
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,147
    Location
    I am sorry, that helps me not at all. I am not clear as to what you want to do.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  16. #16
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    3,837
    Location
    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.
    Remember To Do the Following....
    Use tags when posting code to the thread,
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  17. #17
    VBAX Regular
    Joined
    Feb 2010
    Posts
    33
    Location
    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.

  18. #18
    VBAX Regular
    Joined
    Feb 2010
    Posts
    33
    Location
    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]

  19. #19

  20. #20
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,147
    Location
    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?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •