Solved Assistance on how to return text on a document with blank ce

May 6, 2015 at 10:41:52
Specs: Linux x86_64)
On the same issue I would like to return text which corresponds with the amount but appears on the next column
Jack 1234
Blank Blank
Blank Blank
Martin 234
Blank Blank
Mark 5436

See More: Assistance on how to return text on a document with blank ce

Report •


✔ Best Answer
May 12, 2015 at 10:25:00
Try this untested version. I don't have acess to your file right now, but I'm pretty sure this should work.

Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if the date was entered into specified cell
 If Target.Address = "$A$7" Then
  Application.EnableEvents = False
'Clear old data from cells in Table
   Range("$B$10:$C" & Rows.Count).ClearContents
'Find the Date in Sheet 1Row 1
     With Sheets(1).Rows(1)
       Set d = .Find(Target)
        If Not d Is Nothing Then
'Loop through Sheet 1 table, pulling non-blank data
       lastRw = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
          For Each cell In _
              Sheets(1).Range(Sheets(1).Cells(2, d.Column), _
              Sheets(1).Cells(lastRw, d.Column))
           If cell <> "" Then
            nxtrw = nxtrw + 1
            Range("$B$9").Offset(nxtrw, 0) = Sheets(1).Cells(cell.Row, 1)
            Range("$B$9").Offset(nxtrw, 1) = Sheets(1).Cells(cell.Row, d.Column)
           End If
          Next
'Display message if Date Not Found
        Else: MsgBox "Date Not Found"
        End If
      End With
''Sum Sheet2 Column C
        If Range("C10") <> "" Then
'Put SUM formula in C31
          Range("B31") = "Total"
          Range("C31").Formula = "=SUM(C10:C30)"
        End If
   Application.EnableEvents = True
 End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03



#1
May 6, 2015 at 12:30:49
I don't quite understand what you are looking for. Are you asking to return the Name in Column A based on the Amount in Column B?

If so, try this formula in D2 to return Martin based on the value in C2:

       A         B           C                  D
1     Name     Amount      Amount              Name
2     Jack      1234        234      =INDEX($A$2:$B$7,MATCH(C2,$B$2:$B$7,0),1)
3
4
5     Martin     234
6
7     Mark      5436

Note: If the value in C2 is not found in B2:B7, you will get a #N/A error. That can be fixed by checking for that error first and returning "Not Found" if desired.

=IF(ISNA(MATCH(C2,$B$2:$B$7,0)),"Not Found",
INDEX($A$2:$B$7,MATCH(C2,$B$2:$B$7,0),1))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
May 6, 2015 at 14:41:40
"On the same issue"
What same issue do you mean?

Always pop back and let us know the outcome - thanks


Report •

#3
May 6, 2015 at 16:47:40
Hi Derek,

Many thanks for your assistance.

Sorry ...What I meant by "on the same issue" refers to a solution you had provided on the excel formulae on how to skip blank cells see link....http://www.computing.net/answers/office/excel-formula-to-skip-blank-cells/10355.html

So my question is how would you return the only text(skip blank cells) that corresponds to the numbers that you have returned with =IF(ISERROR(SMALL($A$1:$A$9,ROW())),"",SMALL($A$1:$A$9,ROW())) as per the link attached.

Please note that text appears on one spreadsheet and I want it to be returned on the other sheet if a date changes.

In summary
WorkSheet 1
3/4/2015 4/4/2015 5/4/2015
Jack 1234 342 Blank
Blank Blank Blank Blank
Blank Blank Blank Blank
Martin 234 Blank 654
Blank Blank Blank Blank
Mark 5436 645 634

What I would like it is to appear on worksheet 2 when I type say 5/4/2015 on one cell is

Martin 654
Mark 634

And say if I type 3/4/2015 I would like to see

Jack 1234
Martin 234
Mark 5436

I hope you get the picture of what I want to come up with now and it makes sense.

Thanks
David


message edited by davidmathenge1


Report •

Related Solutions

#4
May 6, 2015 at 17:59:35
Wrong bloke LOL. Keep watching for DerbyDad03 now you've revamped the question. He's brilliant at this. I was just clarifying for my own benefit - sorry.

Always pop back and let us know the outcome - thanks


Report •

#5
May 6, 2015 at 18:09:07
Sorry Derek! I'll check out for DerbyDad03 to crack this one. Its one of those!

Report •

#6
May 7, 2015 at 03:45:00
Please click on the following line and read the instructions on how to post data in this forum. Then either edit your post or repost your data so that it us easier for us to read.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
May 7, 2015 at 04:36:37
Hi DerbyDad03,
Many thanks for your assistance.
Sorry ...What I meant by "on the same issue" refers to a solution you had provided on the excel formulae on how to skip blank cells see link....http://www.computing.net/answers/office/excel-formula-to-skip-blank-cells/10355.html
So my question is how would you return the only text(skip blank cells) that corresponds to the numbers that you have returned with =IF(ISERROR(SMALL($A$1:$A$9,ROW())),"",SMALL($A$1:$A$9,ROW())) as per the link attached.
Please note that text appears on one spreadsheet and I want it to be returned on the other sheet if a date changes.
In summary
WorkSheet 1
	 A        B               C
     03/04/2015	04/04/2015	05/04/2015
    
  1  Jack	1,234.00	342.00	
  2						
  3  Martin	234.00		654.00
  4			
  5  Mark	5,436.00	645.00	          

What I would like it to appear on worksheet 2 when I type 5/4/2015 on one cell is

And say if I type 3/4/2015 I would like to see

        A	            B
    1 Jack 	      1,234.00
    2 Martin 	       234.00
    3 Mark 	      5,436.00

I hope you get the picture of what I want to come up with now and it makes sense.

Thanks
David

message edited by davidmathenge1


Report •

#8
May 7, 2015 at 10:07:34
Your post still doesn't make sense.

re: "What I would like it to appear on worksheet 2 when I type 5/4/2015 on one cell is"

Is what? You have not told us what you want to see.

Your data table doesn't make sense either. You have dates in Columns A:C, but they are not in any Row. You have a name in A1 (Jack) so where are your Dates located?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#9
May 8, 2015 at 04:28:22
To clarify on this,

I have one excel sheet which I normally record the expenses everyday. Column 1 will contain the money a person is given and Row 1 will contain the date of the month. What will keep on changing is the amounts that you give every person. On some days I don't give some persons money meaning the cells are blank. In summary it looks like this

		  A	            B	            C
1		01/05/2015	02/05/2015	03/05/2015
2	Jack	2000	          500	         400
3	Mark 	600		                7000
4	Martin		          7000	
5	James 	5000		                6000
6	Daniel	7000		
7	Elgon		          6000	

On worksheet 2

When I type in 1/5/2015 on one cell in worksheet, I would like it to return something like this:

         A        B
1       Date 	01/05/2015
2       Jack 	 2000
3       Mark	  600
4       James	 5000
5       Daniel   7000

If I change the date to 3/5/2015, I would like it to return
        A         B
1	Date	03/05/2015
2	Jack	 400
3	Mark 	7000
4	James 	6000

I don''t want to see the blank cells without any amounts. Hope you now get what I mean.


Report •

#10
May 8, 2015 at 05:45:17
Sorry, but I still don't think you have it right.

re: Column 1 will contain the money a person is given and Row 1 will contain the date of the month.

Column 1? Do you mean Column A as shown in your table? If so, where are your names located?

Please note that I am being nit-picky about the presentation of your data so that we don't waste time making assumptions about your layout. I could easily assume that your dates are in B1:D1, not A1:C1 as shown, but since I know that the current Row 1 layout is incorrect, I can't really be sure that the rest of your data is laid out correctly. Only you know that for sure.

Please edit your layout, preview the result very carefully and re-post your data making sure that all Column letters and Row numbers line up correctly.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#11
May 8, 2015 at 06:31:09
I wish I would just email you the excel sheet so that it easier for you. Am having troubles aligning the data. Let me explain them to you.

Yes its Column A not Column 1 as I had indicated

The names are on A2:A7

Dates are on B1:D1

The expense amounts are on B2:C7


Report •

#12
May 8, 2015 at 09:11:26
re: "Am having troubles aligning the data. "

You've done a fine job of aligning the columns, you just had the column letters, well, wrong.

          A	    B	            C                D
1		01/05/2015	02/05/2015	03/05/2015
2	Jack	   2000	           500	           400
3	Mark 	    600		                  7000
4	Martin		          7000	
5	James 	   5000		                  6000
6	Daniel	   7000		
7	Elgon		          6000	

That said, I will work on your question based on the table above.

P.S. Files can be uploaded to zippyshare and then the link can be posted back in the forum. Just be sure to eliminate/change any data that you don't want the world to see.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#13
May 8, 2015 at 11:39:26
I don't know if there is a "formula based" solution, but this macro passed my testing.

If you store this code in the Sheet2 VBA module and enter a Date in A1, you should see the requested data in the cells below the Date.

Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if the date was entered into specified cell
 If Target.Address = "$A$1" Then
  Application.EnableEvents = False
'Clear old data from cells below Date
   Range("$A$2:$B" & Rows.Count).ClearContents
'Find the Date in Sheet 1Row 1
     With Sheets(1).Rows(1)
       Set d = .Find(Target)
        If Not d Is Nothing Then
'Loop through Sheet 1 table, pulling non-blank data
       lastRw = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
          For Each cell In _
              Sheets(1).Range(Sheets(1).Cells(2, d.Column), _
              Sheets(1).Cells(lastRw, d.Column))
           If cell <> "" Then
            nxtrw = nxtrw + 1
            Range("$A$1").Offset(nxtrw, 0) = Sheets(1).Cells(cell.Row, 1)
            Range("$A$1").Offset(nxtrw, 1) = Sheets(1).Cells(cell.Row, d.Column)
           End If
          Next
'Display message if Date Not Found
        Else: MsgBox "Date Not Found"
        End If
      End With
   Application.EnableEvents = True
 End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#14
May 9, 2015 at 02:23:02
Woow Excellent!! It worked.

You have saved me quite a bit of some work right there!

Is it possible for us to get a sum of the figures on B2:B7 from the VBA module you've created?

Thanks
David


Report •

#15
May 9, 2015 at 12:48:23
Try this version...


Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if the date was entered into specified cell
 If Target.Address = "$A$1" Then
  Application.EnableEvents = False
'Clear old data from cells below Date
   Range("$A$2:$B" & Rows.Count).ClearContents
'Find the Date in Sheet 1Row 1
     With Sheets(1).Rows(1)
       Set d = .Find(Target)
        If Not d Is Nothing Then
'Loop through Sheet 1 table, pulling non-blank data
       lastRw = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
          For Each cell In _
              Sheets(1).Range(Sheets(1).Cells(2, d.Column), _
              Sheets(1).Cells(lastRw, d.Column))
           If cell <> "" Then
            nxtrw = nxtrw + 1
            Range("$A$1").Offset(nxtrw, 0) = Sheets(1).Cells(cell.Row, 1)
            Range("$A$1").Offset(nxtrw, 1) = Sheets(1).Cells(cell.Row, d.Column)
           End If
          Next
'Display message if Date Not Found
        Else: MsgBox "Date Not Found"
        End If
      End With
''Sum Sheet2 Column B
        If Range("B2") <> "" Then
'Determine last Row with data in Column B and put SUM formula in next Row
         lastRw = Range("B" & Rows.Count).End(xlUp).Row
          Range("A" & lastRw + 1) = "Total"
          Range("B" & lastRw + 1).Formula = "=SUM(B2:B" & lastRw & ")"
        End If
   Application.EnableEvents = True
 End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#16
May 10, 2015 at 21:43:32
Thanks a lot man!

It worked as expected.



Report •

#17
May 10, 2015 at 23:30:59
I have tried to adjust the macro a bit to suit my cells and final document due to the header and other elements as you will see on the link below.

http://www83.zippyshare.com/v/8MiBz...

I adjusted it as below

Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if the date was entered into specified cell
 If Target.Address = "$A$7" Then
  Application.EnableEvents = False
'Clear old data from cells below Date
   Range("$A$2:$B" & Rows.Count).ClearContents
'Find the Date in Sheet 1Row 1
     With Sheets(1).Rows(1)
       Set d = .Find(Target)
        If Not d Is Nothing Then
'Loop through Sheet 1 table, pulling non-blank data
       lastRw = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
          For Each cell In _
              Sheets(1).Range(Sheets(1).Cells(2, d.Column), _
              Sheets(1).Cells(lastRw, d.Column))
           If cell <> "" Then
            nxtrw = nxtrw + 1
            Range("$B$1").Offset(nxtrw, 0) = Sheets(1).Cells(cell.Row, 1)
            Range("$B$1").Offset(nxtrw, 1) = Sheets(1).Cells(cell.Row, d.Column)
           End If
          Next
'Display message if Date Not Found
        Else: MsgBox "Date Not Found"
        End If
      End With
''Sum Sheet2 Column C
        If Range("C10") <> "" Then
'Determine last Row with data in Column B and put SUM formula in next Row
         lastRw = Range("B" & Rows.Count).End(xlUp).Row
          Range("B" & lastRw + 1) = "Total"
          Range("C" & lastRw + 1).Formula = "=SUM(C10:C" & lastRw & ")"
        End If
   Application.EnableEvents = True
 End If
End Sub

Where am I missing something?


Report •

#18
May 11, 2015 at 03:45:53
I am not currently able to access zippyshare, but I noticed one thing in your code.

You did not changed the range from which the old data is cleared. You changed the date cell from A1 to A7, but the code starts clearing data at A2.

I don't know if that is your only problem but I won't be able to access the file for a few hours.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#19
May 11, 2015 at 05:15:00
Thanks for your response.

But unfortunately it didn't work. I guess I will just wait for you to access the file later when you can.

Rgds
David


Report •

#20
May 11, 2015 at 05:37:18
What is happening?

Perhaps this tutorial will help you determine the problem. If you are going to be using macros in your workbooks, you should familiarize yourself with how to troubleshoot them.

http://www.computing.net/howtos/sho...

Note: Since this code is triggered on an event, you have to pause the code after it automatically starts before you can begin to single-step through it. To do this, click in the grey bar next to this line:

 If Target.Address = "$A$7" Then

This should put a brown "dot" in the grey column which means you have inserted a breakpoint. The code will trigger when the date is entered in A7 and then pause at the breakpoint. You can then begin to use the tips in the debugging tutorial.

You can also toggle the breakpoint by selecting any executable line and choosing Toggle Breakpoint under the Debug menu.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#21
May 11, 2015 at 20:09:03
There were 2 things that you didn't change which were causing the code to act badly:

Original line that cleared the data from A2 and down:

'Clear old data from cells below Date
   Range("$A$2:$B" & Rows.Count).ClearContents

Required change to clear data from B10 and down:

'Clear old data from cells in Table
   Range("$B$10:$C" & Rows.Count).ClearContents

Lines you changed to copy data into Columns B & C, but starting in Row 2:

            Range("$B$1").Offset(nxtrw, 0) = Sheets(1).Cells(cell.Row, 1)
            Range("$B$1").Offset(nxtrw, 1) = Sheets(1).Cells(cell.Row, d.Column)

Required lines to copy the data into Columns B & C but starting in Row 10:

            Range("$B$9").Offset(nxtrw, 0) = Sheets(1).Cells(cell.Row, 1)
            Range("$B$9").Offset(nxtrw, 1) = Sheets(1).Cells(cell.Row, d.Column)

However, there is another issue that we need to deal with. In your example data, you had a list of names in Column A. The values you had in Columns B:D went no further down than the row containing the last name (i.e. A7 = Elgon).

In the spreadsheet, you have values in Rows that are below the last entry in Column A. For example, you have a value in G89 but no entry in A89. Based on how the code is written, it will not pick up the value in G89 because the code thinks the data ends at Row 88 (i.e A88 = Ihub job posting, A89 ="")

If Column A can be blank even though there are values further down in the table, the code needs another way to determine where to stop looking for values. I had assumed that there would always be a "name" in Column A, but I guess that's not the case.

Will the table always end at Row 96 as shown on the spreadsheet?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#22
May 12, 2015 at 00:22:02
Derbydad,

Thanks for the response

Yes , there are likely chances of addition of rows on sheet 1 in an event there is additional workers/expenses to add to the list meaning the table will not always end at row 96.

However, there will always be a list of names on column A in sheet one meaning there will always be no need for you to change that code. I just missed to pull some data on the last few rows.

Also I was thinking if we would have the sum total on cell C31 it would look more presentable. I would really appreciate if you send me the complete code once you are done.

Thanks
David


Report •

#23
May 12, 2015 at 04:05:03
re: Also I was thinking if we would have the sum total on cell C31 it would look more presentable.

If you want the sum in C31, then you are guaranteeing that there will never be more than 21 values to sum, correct?

If there are more than 21 values, the sum will overwrite the value in C31 and cause an error.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#24
May 12, 2015 at 04:30:23
re: If you want the sum in C31, then you are guaranteeing that there will never be more than 21 values to sum, correct?

We hardly have more than 21 items on the form so that would be enough space for everything. In case we did it wouldn't even fit on the page.and would have to go onto page 2.


Report •

#25
May 12, 2015 at 10:25:00
✔ Best Answer
Try this untested version. I don't have acess to your file right now, but I'm pretty sure this should work.

Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if the date was entered into specified cell
 If Target.Address = "$A$7" Then
  Application.EnableEvents = False
'Clear old data from cells in Table
   Range("$B$10:$C" & Rows.Count).ClearContents
'Find the Date in Sheet 1Row 1
     With Sheets(1).Rows(1)
       Set d = .Find(Target)
        If Not d Is Nothing Then
'Loop through Sheet 1 table, pulling non-blank data
       lastRw = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
          For Each cell In _
              Sheets(1).Range(Sheets(1).Cells(2, d.Column), _
              Sheets(1).Cells(lastRw, d.Column))
           If cell <> "" Then
            nxtrw = nxtrw + 1
            Range("$B$9").Offset(nxtrw, 0) = Sheets(1).Cells(cell.Row, 1)
            Range("$B$9").Offset(nxtrw, 1) = Sheets(1).Cells(cell.Row, d.Column)
           End If
          Next
'Display message if Date Not Found
        Else: MsgBox "Date Not Found"
        End If
      End With
''Sum Sheet2 Column C
        If Range("C10") <> "" Then
'Put SUM formula in C31
          Range("B31") = "Total"
          Range("C31").Formula = "=SUM(C10:C30)"
        End If
   Application.EnableEvents = True
 End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#26
May 12, 2015 at 10:54:42
Re: Try this untested version. I don't have acess to your file right now, but I'm pretty sure this should work.

Bravo! It worked!

The only thing I added at the end of the code is End Sub.


Report •

#27
May 12, 2015 at 11:10:42
I'm glad it worked out for you.

Now you should play around with the debugging techniques in the tutorial. It best to learn those tecnhiques with a working piece of code so that you'll understand how to use them before you actually need them to troubleshooting code that doesn't work.

http://www.computing.net/howtos/sho...


message edited by DerbyDad03


Report •


Ask Question