Posts by ipbr21054a

Thanks but i am going to leave this and think of another way around it,i couldnt get it flowing as easy as what i have in combobox1

Thanks

Hi,
Yes i do believe it was one of your codes.

I have attached the owrkbook for you to take a look at.
At the top of the POSTAGE worksheet you will se 2 ComboBoxes
ComboBox1 is fine as it is "A-Z FIRST NAME LASTNAME"

ComboBox is the one we need to concentrate on.
It would need to be sorted A-Z BUT Surname Lastname

As like ComboBox1 when a customer is selected you are then taken to that customer of which is in column B

You will see in ComboBox2 not all "But Most OF Them" have a username, example A1 4X4 BREAKERS
You will also see there name ends with 001 002 003 etc etc
This is because if they are a returning customer the 002 003 etc etc is added after there name for each purchase.

Not sure how you will get this working but my main goal would be to quickly look down the list to pick out say BLAKE KEVIN

Thanks & have a nice day.

Hi,
Great help on my first post so now on my second post,thanks all.

I have a ComboBox2 on my worksheet which when i click the drop down arrow it currently has a list of customer sorted A-Z First Name followed by Last Name.

Im looking to have the same sort A-Z BUT Last Name followed by First Name.

Could you assist me with this please.
Have a nice day.

This is the code in use at present.

Private Sub ComboBox2_Change()
  Dim r As Range
  Set r = Range("B7", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible) _
    .Find(ComboBox2.Value)
  If Not r Is Nothing Then r.Select
  ComboBox2.ListIndex = -1
End Sub
Private Sub ComboBox2_DropButtonClick()
  RangeUniqueSortFillControl Range("B7", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible), Sheet13.ComboBox2
End Sub

Thanks very much

Can you advise as I’m not sure about that.

So if my tax year is 5th April I just simply change it to suit.

Thanks.

Can you advise why you have used the dat 2/04/2019

Hi,
Post 09 still cincorrect.
Pleas4e see my post 10

With the month APRIL and the year 2019 i type in G5 01/04/2019 but the value is entered in cell D17

D17 is only for 2020

D5 is only for 2019

Hi,
Just looking at the code.
This line If "CDate(strDate) < CDate("2/04/2019") Then"

I think the year for the code needs to be taken from cell J3 so the code updates itself each year.

My end of tax year is 5th April so on my worksheet i start with April 2019 May 2019 June2019 ............February 2020 March 2020 April 2020

Hope this helps

So the code looks at the date in cell G3 & it also looks at the year in cell J3
It then looks at my first entry in cell G5

Now the code decides if value in G5 is BEFORE or AFTER 5th APRIL of the year in cell J3

Once the code has decided it then places the values in cells D5:E5 or D17:E17 where D5 is the year BEFORE & D17 is the AFTER 5th April of the year in cell J3

Did i explain that correctly for you.

Morning,
Thanks for the input i like how it works but its not quite right but i dont think it will take you long to see the problem

So this is my test.
On the worksheet called G INCOME in cell G3 APRIL is entered
In cell G5 i entered the date 10/04/2020
I then pressed the transfer button.

I then looked at the worksheet G SUMMARY and the value had transfered ok but was placed in the incorrect cell of which was D33
With this code we should ONLY be using the range C5:C17 as that is the INCOME range the range of cells below D21:D33 are EXPENSES range and should not be used.

If i enter in worksheet G INCOME cell G3 10/04/2019 the value MUST be entered in cell D5 because that first APRIL represents 2019

If i had entered in cell G3 22/04/2020 then that value should be entered in cell D17 becuase that second APRIL represents 2020

So the values must ONLY be entered into cells D5 or D17

Hope we can sort this so have a nice day

Please use photo attached.

[ATTACH=JSON]{"data-align":"none","data-size":"full","title":"5562.jpg","data-attachmentid":1221182}[/ATTACH]

Hi,
So below is the working code.

Option Explicit
Private Sub TransferIncomeInfo_Click()
    Dim rFndCell As Range
    Dim strData As String
    Dim stFnd As String
    Dim fRow As Long
    Dim sh As Worksheet
    Dim ws As Worksheet
   
    Set ws = Sheets("G INCOME")
    Set sh = Sheets("G SUMMARY")
    stFnd = ws.Range("G3").Value
    
        With sh
            Set rFndCell = .Range("C5:C17").Find(stFnd, LookIn:=xlValues)
                If Not rFndCell Is Nothing Then
                    fRow = rFndCell.Row
                    ws.Range("J31,K31").Copy sh.Cells(fRow, 4)
                    MsgBox "Transfer Has Been Completed", vbInformation + vbOKOnly, "INCOME TRANSFER SHEET MESSAGE"
                Else
                    MsgBox "DOES NOT EXIST"
            End If
        End With
        
    End Sub

I forgot to ask one thing.
On the G SUMMARY sheet where the values are written etc we have two months of APRIL in column C
Cell C5 is now named APRIL 1 where cell C17 is now named APRIL 2

What can you advise so the code is correctly placed in the correct cells with regards APRIL 1 & APRIL 2

This is for my end of year tax accounts so we have APRIL 1 "being 2019" & APRIL 2 "being 2020"

Can something be added into the code that if the current year is 2019 then when the codes finds the month it knows only to look at APRIL 1 & not APRIL 2 which will only work if the current year is 2020
OR
In cell J3 is the year in question.
Please see attached file above.

Did i explain correctly.
I wish to use the same code each year so the year thing needs to represent the pc year date so i dont need to remember to change it all the time.

Any more info required please advise

Many thanks that worked a treat.

have a nice day

Hi,
sorry abouth that.

File now attached.

Many Thanks

Hi,
I am new to VBA & was trying to follow the post shown here.
https://www.ozgrid.com/forum/f…values-in-the-same-column

I got a little confused & now the values dont end up in the correct cells..
Please could you assist me & some information here to help you.
SHEET 1
Month in cell G3
First Value in cell J31
Second Value in cell K31

SHEET 2
Months are in column range C5:C17
First value to be placed in column D
Second value to be placed in column E

So i was expecting this to happen.
Run macro.
Code looks at SHEET 1 in cell G3 & remembers JULY
Code also looks at value in cell J31 of which is £12.34
Code also looks at value in cell K31 of which is 7

Code then looks at SHEET 2 for a match in column C thus JULY.
Once JULY has been found in the cell to the right now column D paste the first value £12.34
I also then require the second value to be placed in the next cell to the right now column E the second value 7

Here is the code i have in use if you can help please.
Have a nice day.

Option Explicit
Private Sub TransferIncomeInfo_Click()
    Dim rFndCell As Range
    Dim strData As String
    Dim stFnd As String
    Dim fCol As Integer
    Dim sh As Worksheet
    Dim ws As Worksheet
    
    Set ws = Sheets("SHEET 1")
    Set sh = Sheets("SHEET 2")
    stFnd = ws.Range("G3").Value
     
        With sh
            Set rFndCell = .Range("C5:C17").Find(stFnd, LookIn:=xlValues)
                If Not rFndCell Is Nothing Then
                    fCol = rFndCell.Column
                    ws.Range("J31,K31").Copy sh.Cells(6, 4, fCol)
                    MsgBox "Transfer Has Been Completed", vbInformation + vbOKOnly, "INCOME TRANSFER SHEET MESSAGE"
                Else
                    MsgBox "DOES NOT EXIST"
            End If
        End With
         
    End Sub