• 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.

If Statement + Range Named

I

irisqueiroz

Member
Hi,


I'd some help to write a code like this:


For i = 1 to LstRow


If cells(i,3) = any cell in a named range (ex: D1:D52 is a named range "ColumnD") then

'check if cell (i,3) have the same value as any cell in the range "ColumnD"

offset(0,1) "ColumnD" , and send the cell(i,3) to the sheet named the value of column E.


For example (on the file that I attached):


For i = 1 'cells(1,3) is "AUTO"

so I search on the named range "ColumnD" where's the word "AUTO". In this case, it's in the position 1 too. So I offset(0,1) and go to Column E. The value is "Oficina" in Column E, line 1. So i have to send the cell(1,3).value to the sheet named "oficina" (i'll create those sheets).


And so on...


https://www.dropbox.com/s/4uk9m9k6jcqdbqf/example.xlsx?m
 
Hi ,


I think you should give more examples of how exactly you want the output to be ; suppose we take the first five rows of data in your worksheet , which I have copied here :

[pre]
Code: 
LUCA        LUCA        Oficina
AUTO       AUTO       Oficina
AUTO       SOLUVED   Oficina
SOLUVED  JALLTE      Oficina
JALLTE     ALUGUEL   Aluguel
[/pre]
The above data is from columns C , D and E


Can you say which data should go into which sheet tab ?


Narayan
 
So, first i'll check in what position the item of column C is in column D ...


C - Position in D - E


LUCA - 1 - put LUCA in sheet named "oficina"

AUTO - 2 - put AUTO in sheet named "oficina"

AUTO - 2 - put AUTO in sheet named "oficina"

SOLUVED - 3 - put SOLUVED in sheet named "oficina"

JALLTE - 4 - put JALLTE in sheet named "aluguel"
 
Hi ,


As a general example , suppose we have text1 in cell C57 ; suppose this text1 matches with text1 in D33 ; now suppose the text in E33 is XYZ ; then the value text1 will be placed in the tab named XYZ ; can you specify in which cell in XYZ ?


Narayan
 
Hi ,


Can you download this file and see if it is what you wanted ?


http://speedy.sh/uCeZZ/example-Aluguel.xlsm


There are two macros :


1. Create_Sheets - this creates the 11 sheets whose names are present in column E


2. Match_Text - this puts the text that is in column C , into the appropriate cells in column A , in the relevant sheets.


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