• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA: Populate cell based on formula as well as enter manually [SOLVED]

I

inddon

Member
Hello There,

There are 3 columns as below (attached sample file):

1. There are Payment Types(based on Named Range): First & Second
2. Date 1 is entered manually
3. Date 2 is a partially referenced cell as follows:
a. If 'Payment Type' = "First" Then 'Date 2' = 'Date 1'
else cell 'Date 2' should be blank and the user should enter it manually (no validation, if he leaves cell empty).

Could you please advise, how this can be achieved?

Thanks & regards
Don
 

Attachments

  • Reference and Manually entered Date.xlsx
    11.4 KB · Views: 7
I'm not sure what you are asking for Don...it looks like you already have a formula setup that handles this correctly. Can you give an example of it going wrong?
 
Luke M said:
I'm not sure what you are asking for Don...it looks like you already have a formula setup that handles this correctly. Can you give an example of it going wrong?
Click to expand...


Hello Luke,

Thank you for your reply. Yes, it does correctly (partial)

When the Payment Type != "First", Date 2 has a null value and now the User overwrites a formula column and enters the date manually (i.e. Date 2)

What I am looking for is the following:

1. The functionality gets executed without 'Date 2' having any formula and 'Date 2' receives a value when Payment Type="First"

Is such a thing possible?

Regards
Don
 
Hi Don,

I think this seems to work.

Delete The formulas in column E, use below code on the worksheet code, where you have the table.

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub

If Not Intersect(Target, Range("Table1[Payment Type]")) Is Nothing Then
    If Target.Value = "First" Then
        Range("E" & Target.Row).Value = Range("D" & Target.Row)
    Else
        Range("E" & Target.Row).Value = ""
       
    End If
End If

Regards,
 
Somendra Misra said:
Hi Don,

I think this seems to work.

Delete The formulas in column E, use below code on the worksheet code, where you have the table.

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub

If Not Intersect(Target, Range("Table1[Payment Type]")) Is Nothing Then
    If Target.Value = "First" Then
        Range("E" & Target.Row).Value = Range("D" & Target.Row)
    Else
        Range("E" & Target.Row).Value = ""
      
    End If
End If

Regards,
Click to expand...


Hello Somendra,

Thanks a lot for the help.

I was wondering if I could go a step further and give a named range for Date1 and Date2 and use these ranges instead in the code.

eg. Range("Date2" & Target.Row).Value = Range("Date1" & Target.Row)
This gives an error for me.

Could you please guide how this can be achieved in your code?

Regards
Don
 
If Date1 and Date2 are columns/vertical ranges, we need to find the intersection of the Target Row, and the named range. So, could would be:
Intersect(Range("Date2"),Target.Row).Value = Intersect(Range("Date1"),Target.Row)
 
Luke M said:
If Date1 and Date2 are columns/vertical ranges, we need to find the intersection of the Target Row, and the named range. So, could would be:
Intersect(Range("Date2"),Target.Row).Value = Intersect(Range("Date1"),Target.Row)
Click to expand...


Hello Luke,

Thank you for your reply.

I have attached the workbook with your code. It gives an error.

Could you please advise?


Thanks & regards
Don
 

Attachments

  • Reference and Manually entered Date.xlsx.xlsm
    16.7 KB · Views: 4
That's my fault. :( Should have said:
Target.EntireRow
not
Target.Row

Corrected code block:
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub

'After user inputs Date 1, then we need to check if Date 2 should be filled
If Not Intersect(Target, Range("Table1[Date 1]")) Is Nothing Then
    If Intersect(Target.EntireRow, Range("Table1[Payment Type]")) = "First" Then
        Intersect(Range("Date2"), Target.EntireRow).Value = Target.Value
    Else
        Intersect(Range("Date2"), Target.EntireRow).Value = ""
    End If
End If
End Sub
 
Last edited:
Luke M said:
That's my fault. :( Should have said:
Target.EntireRow
not
Target.Row

Corrected code block:
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub

'After user inputs Date 1, then we need to check if Date 2 should be filled
If Not Intersect(Target, Range("Table1[Date 1]")) Is Nothing Then
    If Intersect(Target.EntireRow, Range("Table1[Payment Type]")) = "First" Then
        Intersect(Range("Date2"), Target.EntireRow).Value = Target.Value
    Else
        Intersect(Range("Date2"), Target.EntireRow).Value = ""
    End If
End If
End Sub
Click to expand...


Hello Luke

It works perfect.

When I change the Payment Type to Second, Date 2 doesn't go blank. It works only when you change Date1 column.

So I repeated the code (below) so that it checks both sides on 'Payment Type' as well as 'Date1'

Please advise

Regards
Don


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub
Code: 
'Change Payment Type
If Not Intersect(Target, Range("Table1[Payment Type]")) Is Nothing Then
  If Target.Value = "First" Then
  'Range("Date2" & Target.Row).Value = Range("Date1" & Target.Row)
  Intersect(Range("Date2"), Target.EntireRow).Value = Intersect(Range("Date1"), Target.EntireRow)
  Else
  'Range("Date2" & Target.Row).Value = ""
  Intersect(Range("Date2"), Target.EntireRow).Value = ""
  End If
End If

'Change Date1
'After user inputs Date 1, then we need to check if Date 2 should be filled
If Not Intersect(Target, Range("Table1[Date 1]")) Is Nothing Then
  If Intersect(Target.EntireRow, Range("Table1[Payment Type]")) = "First" Then
  Intersect(Range("Date2"), Target.EntireRow).Value = Target.Value
  Else
  Intersect(Range("Date2"), Target.EntireRow).Value = ""
  End If
End If
End Sub
 
What order will users fill in data? Will they put Payment type first, and then Date1, or Date1 and then Payment type?
 
Then I think the setup I have currently will work. Macro gets triggered when user fills in Date. It then checks to see what type it is, and adjusts Date2 accordingly. Or, have I missed something?
 
Luke M said:
Then I think the setup I have currently will work. Macro gets triggered when user fills in Date. It then checks to see what type it is, and adjusts Date2 accordingly. Or, have I missed something?
Click to expand...


Hello Luke,

I am attaching the workbook for your reference. Could you have a quick look, please?

regards
Don
 

Attachments

  • Reference and Manually entered Date.xlsm
    19.2 KB · Views: 1
Hi Don,

I tried to clean out everything, start with a clean slate. Macro now gets fired if user changes Payment Type, OR if they change the Date 1.
 

Attachments

  • ChangeEvent.xlsm
    16.8 KB · Views: 4
Luke M said:
Hi Don,

I tried to clean out everything, start with a clean slate. Macro now gets fired if user changes Payment Type, OR if they change the Date 1.
Click to expand...


Hello Luke,

Thanks a lot for taking the time to do it from the start

Your code works perfect and I have learned something new from you today :)

Regards
Don
 
Luke M said:
Hi Don,

I tried to clean out everything, start with a clean slate. Macro now gets fired if user changes Payment Type, OR if they change the Date 1.
Click to expand...


Hello Luke,

Could you please mark this post as [Solved] for me

Thanks
Don
 
You must log in or register to reply here.
Back
Top