Posts by Elfabb

Elfabb · Sep 28th 2016

Re: Dynamic Chart Range

Hi skywriter, thanks for your reply.

That's more or less what I wanted. I'll try to explain with the image attached.

Scenario 1 is the one I have initially. What I want, is to input both the "2016 Revenue" and "Yahoo" fields manually and the chart update itself to the way it is presented automatically.

For what I have searched, the way out is using what you sent in your post, but I don't know exactly how to implement it. Do you have any suggestions?

Best,

LF

Elfabb · Sep 27th 2016

Hi guys,

So.. I have to do a dynamic bar chart for my work and I'm not managing to get it done. I looked up how to do the dynamic range - most cases the main solution is by using the OFFSET function - but in my case both the series and the values are dynamic, in a way that it doesn't seem to fully work for me. Just to clarify, here's an example:

Baseline

- X Y Z
A 1 2 3
B 4 5 6
C 7 8 9

The bold values below are the ones that are probably going to be added, for instance:

Updated

- X Y Z T
A 1 2 3 4
B 4 5 6 7
C 7 8 9 10
D 1 2 3 4

Is there a way to make set up a formula that will automatically add this new range on the chart?

Best,

LF

PS. I have this same post on the following forum: http://www.mrexcel.com/forum/e…-dynamic-chart-range.html

Elfabb · May 24th 2016

Re: Matching keywords with Product Description

Oh, thank you for the new code! And yes, it's a partial match that I wanted

Elfabb · May 24th 2016

Re: Matching keywords with Product Description

That's exactly it, Fuzz-Head!!! Thank you so much for your help!!!

It's returning some type of error when I have just one keyword on the list, but since that's not going to be the case, that's fine. Also, I'll try to understand your code better for me to change it in case it's needed.

But again, thank you very much! I'll owe you one

Bests,

Elfabb · May 24th 2016

Re: Matching keywords with Product Description

Yes, that's correct! But I want to search the keywords only on Column D (Product Description), Column B is not needed. Also, I forgot to add a remove duplicates mechanism on the end of the macro, since I believe there'll be duplicates on the list if I input two different keywords that appear on the same Product Description..

Elfabb · May 24th 2016

Re: Matching keywords with Product Description

Hi Fuzz-Head,

Thank you for the reply!! Please find attached the database with the new list I'm trying to create. I used the same specification as in the OP. Just for you to know, there are 23k+ entries and they are in portuguese, don't know if it's going to be of any problem to you. I hope you can work something out..

Again, thank you for the support!

forum.ozgrid.com/index.php?attachment/69223/

Elfabb · May 24th 2016

Hi everyone,

I have a database on Excel that is basically a compilation of three information from different sectors of the economy: Product Description, Product Code and Supplier. My aim is to extract from that worksheet (to another one) the information regarding only the sector that I need. Unfortunately, there is no filter whatsoever and the only way for me to identify the products that I want is by the Product Description, that has some keywords associated with the sector that I'm interested. I created a list of keywords that the Product Description might have and also a macro to go through this list and match those Product's Descriptions that contain it. After it matches something, it creates an entry on the new list that I'm building (to have only the products that I need).

The code is the one that follows:

Code

Sub MacroName()
    Dim li_Line_1 As Integer
    Dim li_Line_2 As Integer
    Dim li_Line_3 As Integer
 
    li_Line_1 = 2
    li_Line_2 = 3
    li_Line_3 = 3
   
    Do While Worksheets("WksInput").Cells(li_Line_1, 3) <> Empty
        Do While Worksheets("WksOutput").Cells(li_Line_2, 6) <> Empty
            Worksheets("WksOutput").Range("C" & li_Line_3).Select
                ActiveCell.Formula = "=SEARCH('WksOutput'!F" & li_Line_2 & ", 'WksInput'!E" & li_Line_1 & ")"
                If IsNumeric(ActiveCell) = True Then
                ActiveCell.Formula = "=CELL(""contents"",'WksInput'!E" & li_Line_1 & ")"
                Worksheets("WksOutput").Range("B" & li_Line_3).Select
                ActiveCell.Formula = "=CELL(""contents"",'WksInput'!C" & li_Line_1 & ")"
                Worksheets("WksOutput").Range("D" & li_Line_3).Select
                ActiveCell.Formula = "=CELL(""contents"",'WksInput'!B" & li_Line_1 & ")"
                li_Line_3 = li_Line_3 + 1
                li_Line_1 = li_Line_1 + 1
                Else
                li_Line_2 = li_Line_2 + 1
                li_Line_3 = li_Line_3
                End If
        Loop
    li_Line_2 = 3
    li_Line_1 = li_Line_1 + 1
    Loop
End Sub

Display More

Where:

Line_1 = first line of the data base
Line_2 = first line of keywords' list
Line_3 = first line of the new list (with the products that I want)

WksInput = worksheet of the data base
WksOutput = worksheet of the new list

As you guys can see, it's an awful code (I have no experience at all at programming). It works, but it's a lot slow and it must have a simpler way of doing this. I used Do While because I'll be updating the database and the keywords' list regularly, so I thought it was a good idea for saving time on the future.

Could you guys help me? Any tips will be welcome. Also, I hope I've made myself clear. I can try detailing a bit more if needed.

Thanks a lot!!!