....linda said:ok,got it!thanks Max=)
Max said:Welcome, Linda.
Suggest you put in a fresh, new posting for your new query, perhaps in
excel.programming newsgroup
linda said:how to copy data to another worksheet?i dont want to use the
usual copy-paste cause i want to make it automatic. i have a
worksheet that contains all the raw data[column A-O] that i got
it from database.i need to copy column B & O to another worksheet
and from there the data will be analyze and display it in a
chart. the raw data will be change everytime i update it and
so it will update copied data in the second worksheet and thus
update the chart.can it be done?how?
Max said:You could try this in a copy of the earlier sample file provided ..
In Sheet1,
In C1:
=IF(A1="","",IF(COUNTIF(A$1:A1,A1)>1,"",ROW()))
Copy C1 to D1, fill down to cover the max expected extent of source data in
cols A and B
In Sheet2,
In B1:
=IF(COLUMNS($A:A)>COUNT(Sheet1!$D:$D),"",INDEX(Sheet1!$B:$B,SMALL(Sheet1!$D:$D,COLUMNS($A:A))))
Copy B1 across by as many cols as there are unique mods expected
In A2:
=IF(ROWS($1:1)>COUNT(Sheet1!C:C),"",INDEX(Sheet1!A:A,SMALL(Sheet1!C:C,ROWS($1:1))))
Copy A2 down by as many rows as there are unique custs expected
In B2:
=IF(OR(B$1="",$A2=""),"",SUMPRODUCT((Sheet1!$A$1:$A$100=$A2)*(Sheet1!$B$1:$B$100=B$1)))
Copy B2 across/fill down to populate the grid
(above is a slightly revised version of the earlier formula)
---
In B2:
=IF(OR(B$1="",$A2=""),"",SUMPRODUCT((Sheet1!$A$1:$A$100=$A2)*(Sheet1!$B$1:$B$100=B$1)))
linda said:Wow,its really amazing!Great job!Your help is really appreciated,Thanks
James=)
can i ask you some more?how to copy data to another worksheet using macro?
i have a worksheet that contains all the raw data[column A-O] that i get
from database.i need to copy column B & O to another worksheet and from
there
the data will be analyze and display it in a chart.
the raw data will be change everytime i update it and so it will update
copied data in the second worksheet and thus update the chart.can it be
done?
--
Regards,
Linda
Zone said:Hi Linda,
This macro assumes that:
1. The original table begins in cell A1 of the first worksheet and has
at
least one blank row after it and at least one blank column to the right
of
it.
2. The workbook has a second worksheet available, and the new table will
be
the only thing on the second worksheet.
If these assumptions are correct,
1. Open the workbook in Excel
2. Show the code editor by pressing Alt-F11
3. If the workbook doesn't have a regular code module, insert one by
clicking Insert on the menubar, then Module.
4. Copy the code below and paste it in the module.
5. Press Alt-F11 to return to the spreadsheet.
6. To run the macro, click Tools on the menubar, then Macro, then
Macros,
and select CustModTable from the list, then Run.
Post back and let me know if the code works for you. If so, we'll add a
button to run it.
James
Sub CustModTable()
Dim FromRow As Long, FromCol As Integer, c As Range, c2 As Range
Dim ToRow As Long, ToCol As Integer, This As Variant, This2 As
Variant
Worksheets(2).Activate
Cells.ClearContents
ToRow = 2: ToCol = 1: FromCol = 1
With Worksheets(1)
For FromRow = 1 To .Cells(1, "a").End(xlDown).Row
This = .Cells(FromRow, FromCol)
Set c = Cells.Find(This, LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
Cells(ToRow, ToCol) = This
ToRow = ToRow + 1
End If
Next FromRow
[a1] = "Cust"
ToRow = 1: ToCol = 2: FromCol = 2
For FromRow = 1 To .Cells(1, "b").End(xlDown).Row
This = .Cells(FromRow, FromCol)
Set c = Cells.Find(This, LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
Cells(ToRow, ToCol) = This
ToCol = ToCol + 1
End If
Next FromRow
For ToRow = 2 To Cells(1, "a").End(xlDown).Row
For ToCol = 2 To Cells(1, 1).End(xlToRight).Column
Cells(ToRow, ToCol) = 0
Next ToCol
Next ToRow
For FromRow = 1 To .Cells(1, "b").End(xlDown).Row
This = .Cells(FromRow, "a")
This2 = .Cells(FromRow, "b")
Set c = Cells.Find(This, LookIn:=xlValues, lookat:=xlWhole)
Set c2 = Cells.Find(This2, LookIn:=xlValues, lookat:=xlWhole)
Cells(c.Row, c2.Column) = Cells(c.Row, c2.Column) + 1
Next FromRow
End With
End Sub
linda said:Hi James!
hope you can send me the code cause i need to do it in macro..using
wizard
doesnt solve my problem.
your help is really appreciated.
--
Regards,
Linda
:
Linda, you have had some good replies. However, if you still want to
do
this with a macro, post back and I'll post the code. James
hi!
i've got a table in excel worksheet which part of it looks like
this:
columnA|column B|
cust1 | mod1
cust2 | mod2
cust1 | mod1
cust1 | mod2
cust2 | mod3
cust2 | mod3
but,i need to recreate the table in a new worksheet to be analyze,to
create
chart from the data..this is what i expect:
cust | mod1 | mod2 | mod3 |
cust1| 2 | 1 |
cust2| 0 | 1 | 2
i've try to use filter,but still can't do this..i dont want to
create
it
using pivot table cause i want it to be static..i hope to do this in
macro
cause i want to assign it to a button but i'm not really familiar
with
macro..so,i dont have any idea to solve my this.is there any
solution
for
this?if yes,how?
thanks in advanced.
Max said:Suggest you put in as a fresh, new posting for this, Linda. In your new
posting, you could paste the actual formulas that you're using. It'll be
easier for responders to diagnose what's wrong.
Generally, ensure that ranges used are identically sized and sufficiently
cover the max expected source ranges.
And if you're receiving zero returns where it obviously shouldn't be, look
out for possible data consistency issues, eg: extra "invisible" white
spaces -- as mentioned earlier in this thread -- where the liberal use of
TRIM around the source ranges and the lookup values in the sumproduct would
then help improve robustness of matching and correct returns.
Example
You could try instead in B2, with TRIM:
=IF(OR(TRIM(B$1)="",TRIM($A2)=""),"",SUMPRODUCT((TRIM(Sheet1!$A$1:$A$100)=TRIM($A2))*(TRIM(Sheet1!$B$1:$B$100)=TRIM(B$1))))
Zone said:Glad it works for you, Linda. As a matter of curiosity, how many Custs and
Mods did you have? It should accommodate any reasonable number of these.
On your question about copying columns, if you want the copy to
automatically update everytime the original changes, you'll want to use
references instead of the actual values. Say your original data begins in
cell A1 of Sheet1 and you want your copy to begin in cell A1 of Sheet3. In
A1 of Sheet3, type in
=Sheet1!A1
then drag down the column as far as needed. In B1 of Sheet3, type in
=Sheet1!B1
and drag down the column as far as needed. I don't see why you would need a
macro for this unless the length of the columns is changing when you refresh
the data. If so, post back and we'll tackle that. Cheers! James
linda said:Wow,its really amazing!Great job!Your help is really appreciated,Thanks
James=)
can i ask you some more?how to copy data to another worksheet using macro?
i have a worksheet that contains all the raw data[column A-O] that i get
from database.i need to copy column B & O to another worksheet and from
there
the data will be analyze and display it in a chart.
the raw data will be change everytime i update it and so it will update
copied data in the second worksheet and thus update the chart.can it be
done?
--
Regards,
Linda
Zone said:Hi Linda,
This macro assumes that:
1. The original table begins in cell A1 of the first worksheet and has
at
least one blank row after it and at least one blank column to the right
of
it.
2. The workbook has a second worksheet available, and the new table will
be
the only thing on the second worksheet.
If these assumptions are correct,
1. Open the workbook in Excel
2. Show the code editor by pressing Alt-F11
3. If the workbook doesn't have a regular code module, insert one by
clicking Insert on the menubar, then Module.
4. Copy the code below and paste it in the module.
5. Press Alt-F11 to return to the spreadsheet.
6. To run the macro, click Tools on the menubar, then Macro, then
Macros,
and select CustModTable from the list, then Run.
Post back and let me know if the code works for you. If so, we'll add a
button to run it.
James
Sub CustModTable()
Dim FromRow As Long, FromCol As Integer, c As Range, c2 As Range
Dim ToRow As Long, ToCol As Integer, This As Variant, This2 As
Variant
Worksheets(2).Activate
Cells.ClearContents
ToRow = 2: ToCol = 1: FromCol = 1
With Worksheets(1)
For FromRow = 1 To .Cells(1, "a").End(xlDown).Row
This = .Cells(FromRow, FromCol)
Set c = Cells.Find(This, LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
Cells(ToRow, ToCol) = This
ToRow = ToRow + 1
End If
Next FromRow
[a1] = "Cust"
ToRow = 1: ToCol = 2: FromCol = 2
For FromRow = 1 To .Cells(1, "b").End(xlDown).Row
This = .Cells(FromRow, FromCol)
Set c = Cells.Find(This, LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
Cells(ToRow, ToCol) = This
ToCol = ToCol + 1
End If
Next FromRow
For ToRow = 2 To Cells(1, "a").End(xlDown).Row
For ToCol = 2 To Cells(1, 1).End(xlToRight).Column
Cells(ToRow, ToCol) = 0
Next ToCol
Next ToRow
For FromRow = 1 To .Cells(1, "b").End(xlDown).Row
This = .Cells(FromRow, "a")
This2 = .Cells(FromRow, "b")
Set c = Cells.Find(This, LookIn:=xlValues, lookat:=xlWhole)
Set c2 = Cells.Find(This2, LookIn:=xlValues, lookat:=xlWhole)
Cells(c.Row, c2.Column) = Cells(c.Row, c2.Column) + 1
Next FromRow
End With
End Sub
Hi James!
hope you can send me the code cause i need to do it in macro..using
wizard
doesnt solve my problem.
your help is really appreciated.
--
Regards,
Linda
:
Linda, you have had some good replies. However, if you still want to
do
this with a macro, post back and I'll post the code. James
hi!
i've got a table in excel worksheet which part of it looks like
this:
columnA|column B|
cust1 | mod1
cust2 | mod2
cust1 | mod1
cust1 | mod2
cust2 | mod3
cust2 | mod3
but,i need to recreate the table in a new worksheet to be analyze,to
create
chart from the data..this is what i expect:
cust | mod1 | mod2 | mod3 |
cust1| 2 | 1 |
cust2| 0 | 1 | 2
i've try to use filter,but still can't do this..i dont want to
create
it
using pivot table cause i want it to be static..i hope to do this in
macro
cause i want to assign it to a button but i'm not really familiar
with
macro..so,i dont have any idea to solve my this.is there any
solution
for
this?if yes,how?
thanks in advanced.
Dallman Ross said:linda said:how to copy data to another worksheet?i dont want to use the
usual copy-paste cause i want to make it automatic. i have a
worksheet that contains all the raw data[column A-O] that i got
it from database.i need to copy column B & O to another worksheet
and from there the data will be analyze and display it in a
chart. the raw data will be change everytime i update it and
so it will update copied data in the second worksheet and thus
update the chart.can it be done?how?
A good source of excellent working examples for this -- one of
which I myself was able to use successfully for a similar purpose
last month -- is here:
http://www.rondebruin.nl/tips.htm
In any case, I agree that Max gives great formula help.
=dman=
Zone said:Glad it works for you, Linda. As a matter of curiosity, how many Custs and
Mods did you have? It should accommodate any reasonable number of these.
On your question about copying columns, if you want the copy to
automatically update everytime the original changes, you'll want to use
references instead of the actual values. Say your original data begins in
cell A1 of Sheet1 and you want your copy to begin in cell A1 of Sheet3. In
A1 of Sheet3, type in
=Sheet1!A1
then drag down the column as far as needed. In B1 of Sheet3, type in
=Sheet1!B1
and drag down the column as far as needed. I don't see why you would need a
macro for this unless the length of the columns is changing when you refresh
the data. If so, post back and we'll tackle that. Cheers! James
linda said:Wow,its really amazing!Great job!Your help is really appreciated,Thanks
James=)
can i ask you some more?how to copy data to another worksheet using macro?
i have a worksheet that contains all the raw data[column A-O] that i get
from database.i need to copy column B & O to another worksheet and from
there
the data will be analyze and display it in a chart.
the raw data will be change everytime i update it and so it will update
copied data in the second worksheet and thus update the chart.can it be
done?
--
Regards,
Linda
Zone said:Hi Linda,
This macro assumes that:
1. The original table begins in cell A1 of the first worksheet and has
at
least one blank row after it and at least one blank column to the right
of
it.
2. The workbook has a second worksheet available, and the new table will
be
the only thing on the second worksheet.
If these assumptions are correct,
1. Open the workbook in Excel
2. Show the code editor by pressing Alt-F11
3. If the workbook doesn't have a regular code module, insert one by
clicking Insert on the menubar, then Module.
4. Copy the code below and paste it in the module.
5. Press Alt-F11 to return to the spreadsheet.
6. To run the macro, click Tools on the menubar, then Macro, then
Macros,
and select CustModTable from the list, then Run.
Post back and let me know if the code works for you. If so, we'll add a
button to run it.
James
Sub CustModTable()
Dim FromRow As Long, FromCol As Integer, c As Range, c2 As Range
Dim ToRow As Long, ToCol As Integer, This As Variant, This2 As
Variant
Worksheets(2).Activate
Cells.ClearContents
ToRow = 2: ToCol = 1: FromCol = 1
With Worksheets(1)
For FromRow = 1 To .Cells(1, "a").End(xlDown).Row
This = .Cells(FromRow, FromCol)
Set c = Cells.Find(This, LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
Cells(ToRow, ToCol) = This
ToRow = ToRow + 1
End If
Next FromRow
[a1] = "Cust"
ToRow = 1: ToCol = 2: FromCol = 2
For FromRow = 1 To .Cells(1, "b").End(xlDown).Row
This = .Cells(FromRow, FromCol)
Set c = Cells.Find(This, LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
Cells(ToRow, ToCol) = This
ToCol = ToCol + 1
End If
Next FromRow
For ToRow = 2 To Cells(1, "a").End(xlDown).Row
For ToCol = 2 To Cells(1, 1).End(xlToRight).Column
Cells(ToRow, ToCol) = 0
Next ToCol
Next ToRow
For FromRow = 1 To .Cells(1, "b").End(xlDown).Row
This = .Cells(FromRow, "a")
This2 = .Cells(FromRow, "b")
Set c = Cells.Find(This, LookIn:=xlValues, lookat:=xlWhole)
Set c2 = Cells.Find(This2, LookIn:=xlValues, lookat:=xlWhole)
Cells(c.Row, c2.Column) = Cells(c.Row, c2.Column) + 1
Next FromRow
End With
End Sub
Hi James!
hope you can send me the code cause i need to do it in macro..using
wizard
doesnt solve my problem.
your help is really appreciated.
--
Regards,
Linda
:
Linda, you have had some good replies. However, if you still want to
do
this with a macro, post back and I'll post the code. James
hi!
i've got a table in excel worksheet which part of it looks like
this:
columnA|column B|
cust1 | mod1
cust2 | mod2
cust1 | mod1
cust1 | mod2
cust2 | mod3
cust2 | mod3
but,i need to recreate the table in a new worksheet to be analyze,to
create
chart from the data..this is what i expect:
cust | mod1 | mod2 | mod3 |
cust1| 2 | 1 |
cust2| 0 | 1 | 2
i've try to use filter,but still can't do this..i dont want to
create
it
using pivot table cause i want it to be static..i hope to do this in
macro
cause i want to assign it to a button but i'm not really familiar
with
macro..so,i dont have any idea to solve my this.is there any
solution
for
this?if yes,how?
thanks in advanced.
linda said:Hi James!
i just discover a problem using the copy function from your last post.my 2
columns,cust & mod,contain that copy function because i got the data from
other worksheet before it is been analyze.
therefore,when i run macro CusModTable,its include the cell without value
since the cell still contain formula(=Sheet1!A1).do you have any idea to
solve this?
thanks!
--
Regards,
Linda
Zone said:Glad it works for you, Linda. As a matter of curiosity, how many Custs
and
Mods did you have? It should accommodate any reasonable number of these.
On your question about copying columns, if you want the copy to
automatically update everytime the original changes, you'll want to use
references instead of the actual values. Say your original data begins
in
cell A1 of Sheet1 and you want your copy to begin in cell A1 of Sheet3.
In
A1 of Sheet3, type in
=Sheet1!A1
then drag down the column as far as needed. In B1 of Sheet3, type in
=Sheet1!B1
and drag down the column as far as needed. I don't see why you would
need a
macro for this unless the length of the columns is changing when you
refresh
the data. If so, post back and we'll tackle that. Cheers! James
linda said:Wow,its really amazing!Great job!Your help is really appreciated,Thanks
James=)
can i ask you some more?how to copy data to another worksheet using
macro?
i have a worksheet that contains all the raw data[column A-O] that i
get
from database.i need to copy column B & O to another worksheet and from
there
the data will be analyze and display it in a chart.
the raw data will be change everytime i update it and so it will update
copied data in the second worksheet and thus update the chart.can it be
done?
--
Regards,
Linda
:
Hi Linda,
This macro assumes that:
1. The original table begins in cell A1 of the first worksheet and
has
at
least one blank row after it and at least one blank column to the
right
of
it.
2. The workbook has a second worksheet available, and the new table
will
be
the only thing on the second worksheet.
If these assumptions are correct,
1. Open the workbook in Excel
2. Show the code editor by pressing Alt-F11
3. If the workbook doesn't have a regular code module, insert one by
clicking Insert on the menubar, then Module.
4. Copy the code below and paste it in the module.
5. Press Alt-F11 to return to the spreadsheet.
6. To run the macro, click Tools on the menubar, then Macro, then
Macros,
and select CustModTable from the list, then Run.
Post back and let me know if the code works for you. If so, we'll add
a
button to run it.
James
Sub CustModTable()
Dim FromRow As Long, FromCol As Integer, c As Range, c2 As Range
Dim ToRow As Long, ToCol As Integer, This As Variant, This2 As
Variant
Worksheets(2).Activate
Cells.ClearContents
ToRow = 2: ToCol = 1: FromCol = 1
With Worksheets(1)
For FromRow = 1 To .Cells(1, "a").End(xlDown).Row
This = .Cells(FromRow, FromCol)
Set c = Cells.Find(This, LookIn:=xlValues,
lookat:=xlWhole)
If c Is Nothing Then
Cells(ToRow, ToCol) = This
ToRow = ToRow + 1
End If
Next FromRow
[a1] = "Cust"
ToRow = 1: ToCol = 2: FromCol = 2
For FromRow = 1 To .Cells(1, "b").End(xlDown).Row
This = .Cells(FromRow, FromCol)
Set c = Cells.Find(This, LookIn:=xlValues,
lookat:=xlWhole)
If c Is Nothing Then
Cells(ToRow, ToCol) = This
ToCol = ToCol + 1
End If
Next FromRow
For ToRow = 2 To Cells(1, "a").End(xlDown).Row
For ToCol = 2 To Cells(1, 1).End(xlToRight).Column
Cells(ToRow, ToCol) = 0
Next ToCol
Next ToRow
For FromRow = 1 To .Cells(1, "b").End(xlDown).Row
This = .Cells(FromRow, "a")
This2 = .Cells(FromRow, "b")
Set c = Cells.Find(This, LookIn:=xlValues,
lookat:=xlWhole)
Set c2 = Cells.Find(This2, LookIn:=xlValues,
lookat:=xlWhole)
Cells(c.Row, c2.Column) = Cells(c.Row, c2.Column) + 1
Next FromRow
End With
End Sub
Hi James!
hope you can send me the code cause i need to do it in macro..using
wizard
doesnt solve my problem.
your help is really appreciated.
--
Regards,
Linda
:
Linda, you have had some good replies. However, if you still want
to
do
this with a macro, post back and I'll post the code. James
hi!
i've got a table in excel worksheet which part of it looks like
this:
columnA|column B|
cust1 | mod1
cust2 | mod2
cust1 | mod1
cust1 | mod2
cust2 | mod3
cust2 | mod3
but,i need to recreate the table in a new worksheet to be
analyze,to
create
chart from the data..this is what i expect:
cust | mod1 | mod2 | mod3 |
cust1| 2 | 1 |
cust2| 0 | 1 | 2
i've try to use filter,but still can't do this..i dont want to
create
it
using pivot table cause i want it to be static..i hope to do this
in
macro
cause i want to assign it to a button but i'm not really familiar
with
macro..so,i dont have any idea to solve my this.is there any
solution
for
this?if yes,how?
thanks in advanced.
Zone said:Hi Linda,
If I understand correctly, the problem is that
=Sheet1!A1
returns a 0 if Sheet1!A1 is empty. To avoid this, use
=IF(Sheet1!A1<>"",Sheet1!A1,"")
James
linda said:Hi James!
i just discover a problem using the copy function from your last post.my 2
columns,cust & mod,contain that copy function because i got the data from
other worksheet before it is been analyze.
therefore,when i run macro CusModTable,its include the cell without value
since the cell still contain formula(=Sheet1!A1).do you have any idea to
solve this?
thanks!
--
Regards,
Linda
Zone said:Glad it works for you, Linda. As a matter of curiosity, how many Custs
and
Mods did you have? It should accommodate any reasonable number of these.
On your question about copying columns, if you want the copy to
automatically update everytime the original changes, you'll want to use
references instead of the actual values. Say your original data begins
in
cell A1 of Sheet1 and you want your copy to begin in cell A1 of Sheet3.
In
A1 of Sheet3, type in
=Sheet1!A1
then drag down the column as far as needed. In B1 of Sheet3, type in
=Sheet1!B1
and drag down the column as far as needed. I don't see why you would
need a
macro for this unless the length of the columns is changing when you
refresh
the data. If so, post back and we'll tackle that. Cheers! James
Wow,its really amazing!Great job!Your help is really appreciated,Thanks
James=)
can i ask you some more?how to copy data to another worksheet using
macro?
i have a worksheet that contains all the raw data[column A-O] that i
get
from database.i need to copy column B & O to another worksheet and from
there
the data will be analyze and display it in a chart.
the raw data will be change everytime i update it and so it will update
copied data in the second worksheet and thus update the chart.can it be
done?
--
Regards,
Linda
:
Hi Linda,
This macro assumes that:
1. The original table begins in cell A1 of the first worksheet and
has
at
least one blank row after it and at least one blank column to the
right
of
it.
2. The workbook has a second worksheet available, and the new table
will
be
the only thing on the second worksheet.
If these assumptions are correct,
1. Open the workbook in Excel
2. Show the code editor by pressing Alt-F11
3. If the workbook doesn't have a regular code module, insert one by
clicking Insert on the menubar, then Module.
4. Copy the code below and paste it in the module.
5. Press Alt-F11 to return to the spreadsheet.
6. To run the macro, click Tools on the menubar, then Macro, then
Macros,
and select CustModTable from the list, then Run.
Post back and let me know if the code works for you. If so, we'll add
a
button to run it.
James
Sub CustModTable()
Dim FromRow As Long, FromCol As Integer, c As Range, c2 As Range
Dim ToRow As Long, ToCol As Integer, This As Variant, This2 As
Variant
Worksheets(2).Activate
Cells.ClearContents
ToRow = 2: ToCol = 1: FromCol = 1
With Worksheets(1)
For FromRow = 1 To .Cells(1, "a").End(xlDown).Row
This = .Cells(FromRow, FromCol)
Set c = Cells.Find(This, LookIn:=xlValues,
lookat:=xlWhole)
If c Is Nothing Then
Cells(ToRow, ToCol) = This
ToRow = ToRow + 1
End If
Next FromRow
[a1] = "Cust"
ToRow = 1: ToCol = 2: FromCol = 2
For FromRow = 1 To .Cells(1, "b").End(xlDown).Row
This = .Cells(FromRow, FromCol)
Set c = Cells.Find(This, LookIn:=xlValues,
lookat:=xlWhole)
If c Is Nothing Then
Cells(ToRow, ToCol) = This
ToCol = ToCol + 1
End If
Next FromRow
For ToRow = 2 To Cells(1, "a").End(xlDown).Row
For ToCol = 2 To Cells(1, 1).End(xlToRight).Column
Cells(ToRow, ToCol) = 0
Next ToCol
Next ToRow
For FromRow = 1 To .Cells(1, "b").End(xlDown).Row
This = .Cells(FromRow, "a")
This2 = .Cells(FromRow, "b")
Set c = Cells.Find(This, LookIn:=xlValues,
lookat:=xlWhole)
Set c2 = Cells.Find(This2, LookIn:=xlValues,
lookat:=xlWhole)
Cells(c.Row, c2.Column) = Cells(c.Row, c2.Column) + 1
Next FromRow
End With
End Sub
Hi James!
hope you can send me the code cause i need to do it in macro..using
wizard
doesnt solve my problem.
your help is really appreciated.
--
Regards,
Linda
:
Linda, you have had some good replies. However, if you still want
to
do
this with a macro, post back and I'll post the code. James
hi!
i've got a table in excel worksheet which part of it looks like
this:
columnA|column B|
cust1 | mod1
cust2 | mod2
cust1 | mod1
cust1 | mod2
cust2 | mod3
cust2 | mod3
but,i need to recreate the table in a new worksheet to be
analyze,to
create
chart from the data..this is what i expect:
cust | mod1 | mod2 | mod3 |
cust1| 2 | 1 |
cust2| 0 | 1 | 2
i've try to use filter,but still can't do this..i dont want to
create
it
using pivot table cause i want it to be static..i hope to do this
in
macro
cause i want to assign it to a button but i'm not really familiar
with
macro..so,i dont have any idea to solve my this.is there any
solution
for
this?if yes,how?
thanks in advanced.
linda said:its true i need to use the if statement:
=IF(Sheet1!A1<>"",Sheet1!A1,"")
even the cell contain null value,but then deep inside its actually keep a
function.therefore,when i run CustModTable,the cell contain null value
return
'1'.
cust mod1 mod2 mod3 1 1 1 1 1 1 1 1 1 1 1 1
1
c1 2
c2 3
c3 2 1
i dont know why its just happen to mod List even both column cust & mod
actually contain function.
--
Regards,
Linda
Zone said:Hi Linda,
If I understand correctly, the problem is that
=Sheet1!A1
returns a 0 if Sheet1!A1 is empty. To avoid this, use
=IF(Sheet1!A1<>"",Sheet1!A1,"")
James
linda said:Hi James!
i just discover a problem using the copy function from your last
post.my 2
columns,cust & mod,contain that copy function because i got the data
from
other worksheet before it is been analyze.
therefore,when i run macro CusModTable,its include the cell without
value
since the cell still contain formula(=Sheet1!A1).do you have any idea
to
solve this?
thanks!
--
Regards,
Linda
:
Glad it works for you, Linda. As a matter of curiosity, how many
Custs
and
Mods did you have? It should accommodate any reasonable number of
these.
On your question about copying columns, if you want the copy to
automatically update everytime the original changes, you'll want to
use
references instead of the actual values. Say your original data
begins
in
cell A1 of Sheet1 and you want your copy to begin in cell A1 of
Sheet3.
In
A1 of Sheet3, type in
=Sheet1!A1
then drag down the column as far as needed. In B1 of Sheet3, type in
=Sheet1!B1
and drag down the column as far as needed. I don't see why you would
need a
macro for this unless the length of the columns is changing when you
refresh
the data. If so, post back and we'll tackle that. Cheers! James
Wow,its really amazing!Great job!Your help is really
appreciated,Thanks
James=)
can i ask you some more?how to copy data to another worksheet using
macro?
i have a worksheet that contains all the raw data[column A-O] that i
get
from database.i need to copy column B & O to another worksheet and
from
there
the data will be analyze and display it in a chart.
the raw data will be change everytime i update it and so it will
update
copied data in the second worksheet and thus update the chart.can it
be
done?
--
Regards,
Linda
:
Hi Linda,
This macro assumes that:
1. The original table begins in cell A1 of the first worksheet and
has
at
least one blank row after it and at least one blank column to the
right
of
it.
2. The workbook has a second worksheet available, and the new
table
will
be
the only thing on the second worksheet.
If these assumptions are correct,
1. Open the workbook in Excel
2. Show the code editor by pressing Alt-F11
3. If the workbook doesn't have a regular code module, insert one
by
clicking Insert on the menubar, then Module.
4. Copy the code below and paste it in the module.
5. Press Alt-F11 to return to the spreadsheet.
6. To run the macro, click Tools on the menubar, then Macro, then
Macros,
and select CustModTable from the list, then Run.
Post back and let me know if the code works for you. If so, we'll
add
a
button to run it.
James
Sub CustModTable()
Dim FromRow As Long, FromCol As Integer, c As Range, c2 As
Range
Dim ToRow As Long, ToCol As Integer, This As Variant, This2 As
Variant
Worksheets(2).Activate
Cells.ClearContents
ToRow = 2: ToCol = 1: FromCol = 1
With Worksheets(1)
For FromRow = 1 To .Cells(1, "a").End(xlDown).Row
This = .Cells(FromRow, FromCol)
Set c = Cells.Find(This, LookIn:=xlValues,
lookat:=xlWhole)
If c Is Nothing Then
Cells(ToRow, ToCol) = This
ToRow = ToRow + 1
End If
Next FromRow
[a1] = "Cust"
ToRow = 1: ToCol = 2: FromCol = 2
For FromRow = 1 To .Cells(1, "b").End(xlDown).Row
This = .Cells(FromRow, FromCol)
Set c = Cells.Find(This, LookIn:=xlValues,
lookat:=xlWhole)
If c Is Nothing Then
Cells(ToRow, ToCol) = This
ToCol = ToCol + 1
End If
Next FromRow
For ToRow = 2 To Cells(1, "a").End(xlDown).Row
For ToCol = 2 To Cells(1, 1).End(xlToRight).Column
Cells(ToRow, ToCol) = 0
Next ToCol
Next ToRow
For FromRow = 1 To .Cells(1, "b").End(xlDown).Row
This = .Cells(FromRow, "a")
This2 = .Cells(FromRow, "b")
Set c = Cells.Find(This, LookIn:=xlValues,
lookat:=xlWhole)
Set c2 = Cells.Find(This2, LookIn:=xlValues,
lookat:=xlWhole)
Cells(c.Row, c2.Column) = Cells(c.Row, c2.Column) + 1
Next FromRow
End With
End Sub
Hi James!
hope you can send me the code cause i need to do it in
macro..using
wizard
doesnt solve my problem.
your help is really appreciated.
--
Regards,
Linda
:
Linda, you have had some good replies. However, if you still
want
to
do
this with a macro, post back and I'll post the code. James
hi!
i've got a table in excel worksheet which part of it looks
like
this:
columnA|column B|
cust1 | mod1
cust2 | mod2
cust1 | mod1
cust1 | mod2
cust2 | mod3
cust2 | mod3
but,i need to recreate the table in a new worksheet to be
analyze,to
create
chart from the data..this is what i expect:
cust | mod1 | mod2 | mod3 |
cust1| 2 | 1 |
cust2| 0 | 1 | 2
i've try to use filter,but still can't do this..i dont want to
create
it
using pivot table cause i want it to be static..i hope to do
this
in
macro
cause i want to assign it to a button but i'm not really
familiar
with
macro..so,i dont have any idea to solve my this.is there any
solution
for
this?if yes,how?
thanks in advanced.
Zone said:Linda, yes, I see the problem. Change the last part of the code like this:
For FromRow = 1 To .Cells(1, "b").End(xlDown).Row
This = .Cells(FromRow, "a")
This2 = .Cells(FromRow, "b")
If This <> "" And This2 <> "" Then
Set c = Cells.Find(This, LookIn:=xlValues, lookat:=xlWhole)
Set c2 = Cells.Find(This2, LookIn:=xlValues,
lookat:=xlWhole)
Cells(c.Row, c2.Column) = Cells(c.Row, c2.Column) + 1
End If
Next FromRow
End With
End Sub
This is a rather crude patch but it does seem to fix the problem. James
linda said:its true i need to use the if statement:
=IF(Sheet1!A1<>"",Sheet1!A1,"")
even the cell contain null value,but then deep inside its actually keep a
function.therefore,when i run CustModTable,the cell contain null value
return
'1'.
cust mod1 mod2 mod3 1 1 1 1 1 1 1 1 1 1 1 1
1
c1 2
c2 3
c3 2 1
i dont know why its just happen to mod List even both column cust & mod
actually contain function.
--
Regards,
Linda
Zone said:Hi Linda,
If I understand correctly, the problem is that
=Sheet1!A1
returns a 0 if Sheet1!A1 is empty. To avoid this, use
=IF(Sheet1!A1<>"",Sheet1!A1,"")
James
Hi James!
i just discover a problem using the copy function from your last
post.my 2
columns,cust & mod,contain that copy function because i got the data
from
other worksheet before it is been analyze.
therefore,when i run macro CusModTable,its include the cell without
value
since the cell still contain formula(=Sheet1!A1).do you have any idea
to
solve this?
thanks!
--
Regards,
Linda
:
Glad it works for you, Linda. As a matter of curiosity, how many
Custs
and
Mods did you have? It should accommodate any reasonable number of
these.
On your question about copying columns, if you want the copy to
automatically update everytime the original changes, you'll want to
use
references instead of the actual values. Say your original data
begins
in
cell A1 of Sheet1 and you want your copy to begin in cell A1 of
Sheet3.
In
A1 of Sheet3, type in
=Sheet1!A1
then drag down the column as far as needed. In B1 of Sheet3, type in
=Sheet1!B1
and drag down the column as far as needed. I don't see why you would
need a
macro for this unless the length of the columns is changing when you
refresh
the data. If so, post back and we'll tackle that. Cheers! James
Wow,its really amazing!Great job!Your help is really
appreciated,Thanks
James=)
can i ask you some more?how to copy data to another worksheet using
macro?
i have a worksheet that contains all the raw data[column A-O] that i
get
from database.i need to copy column B & O to another worksheet and
from
there
the data will be analyze and display it in a chart.
the raw data will be change everytime i update it and so it will
update
copied data in the second worksheet and thus update the chart.can it
be
done?
--
Regards,
Linda
:
Hi Linda,
This macro assumes that:
1. The original table begins in cell A1 of the first worksheet and
has
at
least one blank row after it and at least one blank column to the
right
of
it.
2. The workbook has a second worksheet available, and the new
table
will
be
the only thing on the second worksheet.
If these assumptions are correct,
1. Open the workbook in Excel
2. Show the code editor by pressing Alt-F11
3. If the workbook doesn't have a regular code module, insert one
by
clicking Insert on the menubar, then Module.
4. Copy the code below and paste it in the module.
5. Press Alt-F11 to return to the spreadsheet.
6. To run the macro, click Tools on the menubar, then Macro, then
Macros,
and select CustModTable from the list, then Run.
Post back and let me know if the code works for you. If so, we'll
add
a
button to run it.
James
Sub CustModTable()
Dim FromRow As Long, FromCol As Integer, c As Range, c2 As
Range
Dim ToRow As Long, ToCol As Integer, This As Variant, This2 As
Variant
Worksheets(2).Activate
Cells.ClearContents
ToRow = 2: ToCol = 1: FromCol = 1
With Worksheets(1)
For FromRow = 1 To .Cells(1, "a").End(xlDown).Row
This = .Cells(FromRow, FromCol)
Set c = Cells.Find(This, LookIn:=xlValues,
lookat:=xlWhole)
If c Is Nothing Then
Cells(ToRow, ToCol) = This
ToRow = ToRow + 1
End If
Next FromRow
[a1] = "Cust"
ToRow = 1: ToCol = 2: FromCol = 2
For FromRow = 1 To .Cells(1, "b").End(xlDown).Row
This = .Cells(FromRow, FromCol)
Set c = Cells.Find(This, LookIn:=xlValues,
lookat:=xlWhole)
If c Is Nothing Then
Cells(ToRow, ToCol) = This
ToCol = ToCol + 1
End If
Next FromRow
For ToRow = 2 To Cells(1, "a").End(xlDown).Row
For ToCol = 2 To Cells(1, 1).End(xlToRight).Column
Cells(ToRow, ToCol) = 0
Next ToCol
Next ToRow
For FromRow = 1 To .Cells(1, "b").End(xlDown).Row
This = .Cells(FromRow, "a")
This2 = .Cells(FromRow, "b")
Set c = Cells.Find(This, LookIn:=xlValues,
lookat:=xlWhole)
Set c2 = Cells.Find(This2, LookIn:=xlValues,
lookat:=xlWhole)
Cells(c.Row, c2.Column) = Cells(c.Row, c2.Column) + 1
Next FromRow
End With
End Sub
Hi James!
hope you can send me the code cause i need to do it in
macro..using
wizard
doesnt solve my problem.
your help is really appreciated.
--
Regards,
Linda
:
Linda, you have had some good replies. However, if you still
want
to
do
this with a macro, post back and I'll post the code. James
hi!
i've got a table in excel worksheet which part of it looks
like
this:
columnA|column B|
cust1 | mod1
cust2 | mod2
cust1 | mod1
cust1 | mod2
cust2 | mod3
cust2 | mod3
but,i need to recreate the table in a new worksheet to be
analyze,to
create
chart from the data..this is what i expect:
cust | mod1 | mod2 | mod3 |
cust1| 2 | 1 |
cust2| 0 | 1 | 2
i've try to use filter,but still can't do this..i dont want to
create
it
using pivot table cause i want it to be static..i hope to do
this
in
macro
cause i want to assign it to a button but i'm not really
familiar
with
macro..so,i dont have any idea to solve my this.is there any
solution
for
this?if yes,how?
thanks in advanced.
linda said:million of thanks,James!its work!
thank you very much;-)
--
Regards,
Linda
Zone said:Linda, yes, I see the problem. Change the last part of the code like
this:
For FromRow = 1 To .Cells(1, "b").End(xlDown).Row
This = .Cells(FromRow, "a")
This2 = .Cells(FromRow, "b")
If This <> "" And This2 <> "" Then
Set c = Cells.Find(This, LookIn:=xlValues,
lookat:=xlWhole)
Set c2 = Cells.Find(This2, LookIn:=xlValues,
lookat:=xlWhole)
Cells(c.Row, c2.Column) = Cells(c.Row, c2.Column) + 1
End If
Next FromRow
End With
End Sub
This is a rather crude patch but it does seem to fix the problem. James
linda said:its true i need to use the if statement:
=IF(Sheet1!A1<>"",Sheet1!A1,"")
even the cell contain null value,but then deep inside its actually keep
a
function.therefore,when i run CustModTable,the cell contain null value
return
'1'.
cust mod1 mod2 mod3 1 1 1 1 1 1 1 1 1 1 1
1
1
c1 2
c2 3
c3 2 1
i dont know why its just happen to mod List even both column cust & mod
actually contain function.
--
Regards,
Linda
:
Hi Linda,
If I understand correctly, the problem is that
=Sheet1!A1
returns a 0 if Sheet1!A1 is empty. To avoid this, use
=IF(Sheet1!A1<>"",Sheet1!A1,"")
James
Hi James!
i just discover a problem using the copy function from your last
post.my 2
columns,cust & mod,contain that copy function because i got the data
from
other worksheet before it is been analyze.
therefore,when i run macro CusModTable,its include the cell without
value
since the cell still contain formula(=Sheet1!A1).do you have any
idea
to
solve this?
thanks!
--
Regards,
Linda
:
Glad it works for you, Linda. As a matter of curiosity, how many
Custs
and
Mods did you have? It should accommodate any reasonable number of
these.
On your question about copying columns, if you want the copy to
automatically update everytime the original changes, you'll want to
use
references instead of the actual values. Say your original data
begins
in
cell A1 of Sheet1 and you want your copy to begin in cell A1 of
Sheet3.
In
A1 of Sheet3, type in
=Sheet1!A1
then drag down the column as far as needed. In B1 of Sheet3, type
in
=Sheet1!B1
and drag down the column as far as needed. I don't see why you
would
need a
macro for this unless the length of the columns is changing when
you
refresh
the data. If so, post back and we'll tackle that. Cheers! James
Wow,its really amazing!Great job!Your help is really
appreciated,Thanks
James=)
can i ask you some more?how to copy data to another worksheet
using
macro?
i have a worksheet that contains all the raw data[column A-O]
that i
get
from database.i need to copy column B & O to another worksheet
and
from
there
the data will be analyze and display it in a chart.
the raw data will be change everytime i update it and so it will
update
copied data in the second worksheet and thus update the chart.can
it
be
done?
--
Regards,
Linda
:
Hi Linda,
This macro assumes that:
1. The original table begins in cell A1 of the first worksheet
and
has
at
least one blank row after it and at least one blank column to
the
right
of
it.
2. The workbook has a second worksheet available, and the new
table
will
be
the only thing on the second worksheet.
If these assumptions are correct,
1. Open the workbook in Excel
2. Show the code editor by pressing Alt-F11
3. If the workbook doesn't have a regular code module, insert
one
by
clicking Insert on the menubar, then Module.
4. Copy the code below and paste it in the module.
5. Press Alt-F11 to return to the spreadsheet.
6. To run the macro, click Tools on the menubar, then Macro,
then
Macros,
and select CustModTable from the list, then Run.
Post back and let me know if the code works for you. If so,
we'll
add
a
button to run it.
James
Sub CustModTable()
Dim FromRow As Long, FromCol As Integer, c As Range, c2 As
Range
Dim ToRow As Long, ToCol As Integer, This As Variant, This2
As
Variant
Worksheets(2).Activate
Cells.ClearContents
ToRow = 2: ToCol = 1: FromCol = 1
With Worksheets(1)
For FromRow = 1 To .Cells(1, "a").End(xlDown).Row
This = .Cells(FromRow, FromCol)
Set c = Cells.Find(This, LookIn:=xlValues,
lookat:=xlWhole)
If c Is Nothing Then
Cells(ToRow, ToCol) = This
ToRow = ToRow + 1
End If
Next FromRow
[a1] = "Cust"
ToRow = 1: ToCol = 2: FromCol = 2
For FromRow = 1 To .Cells(1, "b").End(xlDown).Row
This = .Cells(FromRow, FromCol)
Set c = Cells.Find(This, LookIn:=xlValues,
lookat:=xlWhole)
If c Is Nothing Then
Cells(ToRow, ToCol) = This
ToCol = ToCol + 1
End If
Next FromRow
For ToRow = 2 To Cells(1, "a").End(xlDown).Row
For ToCol = 2 To Cells(1, 1).End(xlToRight).Column
Cells(ToRow, ToCol) = 0
Next ToCol
Next ToRow
For FromRow = 1 To .Cells(1, "b").End(xlDown).Row
This = .Cells(FromRow, "a")
This2 = .Cells(FromRow, "b")
Set c = Cells.Find(This, LookIn:=xlValues,
lookat:=xlWhole)
Set c2 = Cells.Find(This2, LookIn:=xlValues,
lookat:=xlWhole)
Cells(c.Row, c2.Column) = Cells(c.Row, c2.Column) +
1
Next FromRow
End With
End Sub
Hi James!
hope you can send me the code cause i need to do it in
macro..using
wizard
doesnt solve my problem.
your help is really appreciated.
--
Regards,
Linda
:
Linda, you have had some good replies. However, if you still
want
to
do
this with a macro, post back and I'll post the code. James
hi!
i've got a table in excel worksheet which part of it looks
like
this:
columnA|column B|
cust1 | mod1
cust2 | mod2
cust1 | mod1
cust1 | mod2
cust2 | mod3
cust2 | mod3
but,i need to recreate the table in a new worksheet to be
analyze,to
create
chart from the data..this is what i expect:
cust | mod1 | mod2 | mod3 |
cust1| 2 | 1 |
cust2| 0 | 1 | 2
i've try to use filter,but still can't do this..i dont want
to
create
it
using pivot table cause i want it to be static..i hope to
do
this
in
macro
cause i want to assign it to a button but i'm not really
familiar
with
macro..so,i dont have any idea to solve my this.is there
any
solution
for
this?if yes,how?
thanks in advanced.
Zone said:Glad it worked for you, Linda. Cheers, James
linda said:million of thanks,James!its work!
thank you very much;-)
--
Regards,
Linda
Zone said:Linda, yes, I see the problem. Change the last part of the code like
this:
For FromRow = 1 To .Cells(1, "b").End(xlDown).Row
This = .Cells(FromRow, "a")
This2 = .Cells(FromRow, "b")
If This <> "" And This2 <> "" Then
Set c = Cells.Find(This, LookIn:=xlValues,
lookat:=xlWhole)
Set c2 = Cells.Find(This2, LookIn:=xlValues,
lookat:=xlWhole)
Cells(c.Row, c2.Column) = Cells(c.Row, c2.Column) + 1
End If
Next FromRow
End With
End Sub
This is a rather crude patch but it does seem to fix the problem. James
its true i need to use the if statement:
=IF(Sheet1!A1<>"",Sheet1!A1,"")
even the cell contain null value,but then deep inside its actually keep
a
function.therefore,when i run CustModTable,the cell contain null value
return
'1'.
cust mod1 mod2 mod3 1 1 1 1 1 1 1 1 1 1 1
1
1
c1 2
c2 3
c3 2 1
i dont know why its just happen to mod List even both column cust & mod
actually contain function.
--
Regards,
Linda
:
Hi Linda,
If I understand correctly, the problem is that
=Sheet1!A1
returns a 0 if Sheet1!A1 is empty. To avoid this, use
=IF(Sheet1!A1<>"",Sheet1!A1,"")
James
Hi James!
i just discover a problem using the copy function from your last
post.my 2
columns,cust & mod,contain that copy function because i got the data
from
other worksheet before it is been analyze.
therefore,when i run macro CusModTable,its include the cell without
value
since the cell still contain formula(=Sheet1!A1).do you have any
idea
to
solve this?
thanks!
--
Regards,
Linda
:
Glad it works for you, Linda. As a matter of curiosity, how many
Custs
and
Mods did you have? It should accommodate any reasonable number of
these.
On your question about copying columns, if you want the copy to
automatically update everytime the original changes, you'll want to
use
references instead of the actual values. Say your original data
begins
in
cell A1 of Sheet1 and you want your copy to begin in cell A1 of
Sheet3.
In
A1 of Sheet3, type in
=Sheet1!A1
then drag down the column as far as needed. In B1 of Sheet3, type
in
=Sheet1!B1
and drag down the column as far as needed. I don't see why you
would
need a
macro for this unless the length of the columns is changing when
you
refresh
the data. If so, post back and we'll tackle that. Cheers! James
Wow,its really amazing!Great job!Your help is really
appreciated,Thanks
James=)
can i ask you some more?how to copy data to another worksheet
using
macro?
i have a worksheet that contains all the raw data[column A-O]
that i
get
from database.i need to copy column B & O to another worksheet
and
from
there
the data will be analyze and display it in a chart.
the raw data will be change everytime i update it and so it will
update
copied data in the second worksheet and thus update the chart.can
it
be
done?
--
Regards,
Linda
:
Hi Linda,
This macro assumes that:
1. The original table begins in cell A1 of the first worksheet
and
has
at
least one blank row after it and at least one blank column to
the
right
of
it.
2. The workbook has a second worksheet available, and the new
table
will
be
the only thing on the second worksheet.
If these assumptions are correct,
1. Open the workbook in Excel
2. Show the code editor by pressing Alt-F11
3. If the workbook doesn't have a regular code module, insert
one
by
clicking Insert on the menubar, then Module.
4. Copy the code below and paste it in the module.
5. Press Alt-F11 to return to the spreadsheet.
6. To run the macro, click Tools on the menubar, then Macro,
then
Macros,
and select CustModTable from the list, then Run.
Post back and let me know if the code works for you. If so,
we'll
add
a
button to run it.
James
Sub CustModTable()
Dim FromRow As Long, FromCol As Integer, c As Range, c2 As
Range
Dim ToRow As Long, ToCol As Integer, This As Variant, This2
As
Variant
Worksheets(2).Activate
Cells.ClearContents
ToRow = 2: ToCol = 1: FromCol = 1
With Worksheets(1)
For FromRow = 1 To .Cells(1, "a").End(xlDown).Row
This = .Cells(FromRow, FromCol)
Set c = Cells.Find(This, LookIn:=xlValues,
lookat:=xlWhole)
If c Is Nothing Then
Cells(ToRow, ToCol) = This
ToRow = ToRow + 1
End If
Next FromRow
[a1] = "Cust"
ToRow = 1: ToCol = 2: FromCol = 2
For FromRow = 1 To .Cells(1, "b").End(xlDown).Row
This = .Cells(FromRow, FromCol)
Set c = Cells.Find(This, LookIn:=xlValues,
lookat:=xlWhole)
If c Is Nothing Then
Cells(ToRow, ToCol) = This
ToCol = ToCol + 1
End If
Next FromRow
For ToRow = 2 To Cells(1, "a").End(xlDown).Row
For ToCol = 2 To Cells(1, 1).End(xlToRight).Column
Cells(ToRow, ToCol) = 0
Next ToCol
Next ToRow
For FromRow = 1 To .Cells(1, "b").End(xlDown).Row
This = .Cells(FromRow, "a")
This2 = .Cells(FromRow, "b")
Set c = Cells.Find(This, LookIn:=xlValues,
lookat:=xlWhole)
Set c2 = Cells.Find(This2, LookIn:=xlValues,
lookat:=xlWhole)
Cells(c.Row, c2.Column) = Cells(c.Row, c2.Column) +
1
Next FromRow
End With
End Sub
Hi James!
hope you can send me the code cause i need to do it in
macro..using
wizard
doesnt solve my problem.
your help is really appreciated.
--
Regards,
Linda
:
Linda, you have had some good replies. However, if you still
want
to
do
this with a macro, post back and I'll post the code. James
hi!
i've got a table in excel worksheet which part of it looks
like
this:
columnA|column B|
cust1 | mod1
cust2 | mod2
cust1 | mod1
cust1 | mod2
cust2 | mod3
cust2 | mod3
but,i need to recreate the table in a new worksheet to be
analyze,to
create
chart from the data..this is what i expect:
cust | mod1 | mod2 | mod3 |
cust1| 2 | 1 |
cust2| 0 | 1 | 2
i've try to use filter,but still can't do this..i dont want
to
create
it
using pivot table cause i want it to be static..i hope to
do
this
in
macro
cause i want to assign it to a button but i'm not really
familiar
with
macro..so,i dont have any idea to solve my this.is there
any
solution
for
this?if yes,how?
thanks in advanced.
linda said:Hi James!
its me again,Linda=)
i dont know what happen but i cannot reuse your code anymore.the 1 that
already in use run perfectly but if i want to do it again,the code doesn't
run till end.
the error highlighted on this line:
Set c = Cells.Find(This, LookIn:=xlValues, lookat:=xlWhole)
can u help me if i'am wrong somewhere?
thanks in advanced~
--
Regards,
Linda
Zone said:Glad it worked for you, Linda. Cheers, James
linda said:million of thanks,James!its work!
thank you very much;-)
--
Regards,
Linda
:
Linda, yes, I see the problem. Change the last part of the code like
this:
For FromRow = 1 To .Cells(1, "b").End(xlDown).Row
This = .Cells(FromRow, "a")
This2 = .Cells(FromRow, "b")
If This <> "" And This2 <> "" Then
Set c = Cells.Find(This, LookIn:=xlValues,
lookat:=xlWhole)
Set c2 = Cells.Find(This2, LookIn:=xlValues,
lookat:=xlWhole)
Cells(c.Row, c2.Column) = Cells(c.Row, c2.Column) + 1
End If
Next FromRow
End With
End Sub
This is a rather crude patch but it does seem to fix the problem.
James
its true i need to use the if statement:
=IF(Sheet1!A1<>"",Sheet1!A1,"")
even the cell contain null value,but then deep inside its actually
keep
a
function.therefore,when i run CustModTable,the cell contain null
value
return
'1'.
cust mod1 mod2 mod3 1 1 1 1 1 1 1 1 1 1
1
1
1
c1 2
c2 3
c3 2 1
i dont know why its just happen to mod List even both column cust &
mod
actually contain function.
--
Regards,
Linda
:
Hi Linda,
If I understand correctly, the problem is that
=Sheet1!A1
returns a 0 if Sheet1!A1 is empty. To avoid this, use
=IF(Sheet1!A1<>"",Sheet1!A1,"")
James
Hi James!
i just discover a problem using the copy function from your last
post.my 2
columns,cust & mod,contain that copy function because i got the
data
from
other worksheet before it is been analyze.
therefore,when i run macro CusModTable,its include the cell
without
value
since the cell still contain formula(=Sheet1!A1).do you have any
idea
to
solve this?
thanks!
--
Regards,
Linda
:
Glad it works for you, Linda. As a matter of curiosity, how
many
Custs
and
Mods did you have? It should accommodate any reasonable number
of
these.
On your question about copying columns, if you want the copy to
automatically update everytime the original changes, you'll want
to
use
references instead of the actual values. Say your original data
begins
in
cell A1 of Sheet1 and you want your copy to begin in cell A1 of
Sheet3.
In
A1 of Sheet3, type in
=Sheet1!A1
then drag down the column as far as needed. In B1 of Sheet3,
type
in
=Sheet1!B1
and drag down the column as far as needed. I don't see why you
would
need a
macro for this unless the length of the columns is changing when
you
refresh
the data. If so, post back and we'll tackle that. Cheers!
James
Wow,its really amazing!Great job!Your help is really
appreciated,Thanks
James=)
can i ask you some more?how to copy data to another worksheet
using
macro?
i have a worksheet that contains all the raw data[column A-O]
that i
get
from database.i need to copy column B & O to another worksheet
and
from
there
the data will be analyze and display it in a chart.
the raw data will be change everytime i update it and so it
will
update
copied data in the second worksheet and thus update the
chart.can
it
be
done?
--
Regards,
Linda
:
Hi Linda,
This macro assumes that:
1. The original table begins in cell A1 of the first
worksheet
and
has
at
least one blank row after it and at least one blank column to
the
right
of
it.
2. The workbook has a second worksheet available, and the
new
table
will
be
the only thing on the second worksheet.
If these assumptions are correct,
1. Open the workbook in Excel
2. Show the code editor by pressing Alt-F11
3. If the workbook doesn't have a regular code module,
insert
one
by
clicking Insert on the menubar, then Module.
4. Copy the code below and paste it in the module.
5. Press Alt-F11 to return to the spreadsheet.
6. To run the macro, click Tools on the menubar, then Macro,
then
Macros,
and select CustModTable from the list, then Run.
Post back and let me know if the code works for you. If so,
we'll
add
a
button to run it.
James
Sub CustModTable()
Dim FromRow As Long, FromCol As Integer, c As Range, c2
As
Range
Dim ToRow As Long, ToCol As Integer, This As Variant,
This2
As
Variant
Worksheets(2).Activate
Cells.ClearContents
ToRow = 2: ToCol = 1: FromCol = 1
With Worksheets(1)
For FromRow = 1 To .Cells(1, "a").End(xlDown).Row
This = .Cells(FromRow, FromCol)
Set c = Cells.Find(This, LookIn:=xlValues,
lookat:=xlWhole)
If c Is Nothing Then
Cells(ToRow, ToCol) = This
ToRow = ToRow + 1
End If
Next FromRow
[a1] = "Cust"
ToRow = 1: ToCol = 2: FromCol = 2
For FromRow = 1 To .Cells(1, "b").End(xlDown).Row
This = .Cells(FromRow, FromCol)
Set c = Cells.Find(This, LookIn:=xlValues,
lookat:=xlWhole)
If c Is Nothing Then
Cells(ToRow, ToCol) = This
ToCol = ToCol + 1
End If
Next FromRow
For ToRow = 2 To Cells(1, "a").End(xlDown).Row
For ToCol = 2 To Cells(1,
1).End(xlToRight).Column
Cells(ToRow, ToCol) = 0
Next ToCol
Next ToRow
For FromRow = 1 To .Cells(1, "b").End(xlDown).Row
This = .Cells(FromRow, "a")
This2 = .Cells(FromRow, "b")
Set c = Cells.Find(This, LookIn:=xlValues,
lookat:=xlWhole)
Set c2 = Cells.Find(This2, LookIn:=xlValues,
lookat:=xlWhole)
Cells(c.Row, c2.Column) = Cells(c.Row, c2.Column)
+
1
Next FromRow
End With
End Sub
Hi James!
hope you can send me the code cause i need to do it in
macro..using
wizard
doesnt solve my problem.
your help is really appreciated.
--
Regards,
Linda
:
Linda, you have had some good replies. However, if you
still
want
to
do
this with a macro, post back and I'll post the code.
James
hi!
i've got a table in excel worksheet which part of it
looks
like
this:
columnA|column B|
cust1 | mod1
cust2 | mod2
cust1 | mod1
cust1 | mod2
cust2 | mod3
cust2 | mod3
but,i need to recreate the table in a new worksheet to
be
analyze,to
create
chart from the data..this is what i expect:
cust | mod1 | mod2 | mod3 |
cust1| 2 | 1 |
cust2| 0 | 1 | 2
i've try to use filter,but still can't do this..i dont
want
to
create
it
using pivot table cause i want it to be static..i hope
to
do
this
in
macro
cause i want to assign it to a button but i'm not really
familiar
with
macro..so,i dont have any idea to solve my this.is there
any
solution
for
this?if yes,how?
thanks in advanced.
Zone said:Hi Linda,
Just happened to run across your post. What is the error message? When
the code chokes, rest the mouse over the word This and see what it is.
James
linda said:Hi James!
its me again,Linda=)
i dont know what happen but i cannot reuse your code anymore.the 1 that
already in use run perfectly but if i want to do it again,the code doesn't
run till end.
the error highlighted on this line:
Set c = Cells.Find(This, LookIn:=xlValues, lookat:=xlWhole)
can u help me if i'am wrong somewhere?
thanks in advanced~
--
Regards,
Linda
Zone said:Glad it worked for you, Linda. Cheers, James
million of thanks,James!its work!
thank you very much;-)
--
Regards,
Linda
:
Linda, yes, I see the problem. Change the last part of the code like
this:
For FromRow = 1 To .Cells(1, "b").End(xlDown).Row
This = .Cells(FromRow, "a")
This2 = .Cells(FromRow, "b")
If This <> "" And This2 <> "" Then
Set c = Cells.Find(This, LookIn:=xlValues,
lookat:=xlWhole)
Set c2 = Cells.Find(This2, LookIn:=xlValues,
lookat:=xlWhole)
Cells(c.Row, c2.Column) = Cells(c.Row, c2.Column) + 1
End If
Next FromRow
End With
End Sub
This is a rather crude patch but it does seem to fix the problem.
James
its true i need to use the if statement:
=IF(Sheet1!A1<>"",Sheet1!A1,"")
even the cell contain null value,but then deep inside its actually
keep
a
function.therefore,when i run CustModTable,the cell contain null
value
return
'1'.
cust mod1 mod2 mod3 1 1 1 1 1 1 1 1 1 1
1
1
1
c1 2
c2 3
c3 2 1
i dont know why its just happen to mod List even both column cust &
mod
actually contain function.
--
Regards,
Linda
:
Hi Linda,
If I understand correctly, the problem is that
=Sheet1!A1
returns a 0 if Sheet1!A1 is empty. To avoid this, use
=IF(Sheet1!A1<>"",Sheet1!A1,"")
James
Hi James!
i just discover a problem using the copy function from your last
post.my 2
columns,cust & mod,contain that copy function because i got the
data
from
other worksheet before it is been analyze.
therefore,when i run macro CusModTable,its include the cell
without
value
since the cell still contain formula(=Sheet1!A1).do you have any
idea
to
solve this?
thanks!
--
Regards,
Linda
:
Glad it works for you, Linda. As a matter of curiosity, how
many
Custs
and
Mods did you have? It should accommodate any reasonable number
of
these.
On your question about copying columns, if you want the copy to
automatically update everytime the original changes, you'll want
to
use
references instead of the actual values. Say your original data
begins
in
cell A1 of Sheet1 and you want your copy to begin in cell A1 of
Sheet3.
In
A1 of Sheet3, type in
=Sheet1!A1
then drag down the column as far as needed. In B1 of Sheet3,
type
in
=Sheet1!B1
and drag down the column as far as needed. I don't see why you
would
need a
macro for this unless the length of the columns is changing when
you
refresh
the data. If so, post back and we'll tackle that. Cheers!
James
Wow,its really amazing!Great job!Your help is really
appreciated,Thanks
James=)
can i ask you some more?how to copy data to another worksheet
using
macro?
i have a worksheet that contains all the raw data[column A-O]
that i
get
from database.i need to copy column B & O to another worksheet
and
from
there
the data will be analyze and display it in a chart.
the raw data will be change everytime i update it and so it
will
update
copied data in the second worksheet and thus update the
chart.can
it
be
done?
--
Regards,
Linda
:
Hi Linda,
This macro assumes that:
1. The original table begins in cell A1 of the first
worksheet
and
has
at
least one blank row after it and at least one blank column to
the
right
of
it.
2. The workbook has a second worksheet available, and the
new
table
will
be
the only thing on the second worksheet.
If these assumptions are correct,
1. Open the workbook in Excel
2. Show the code editor by pressing Alt-F11
3. If the workbook doesn't have a regular code module,
insert
one
by
clicking Insert on the menubar, then Module.
4. Copy the code below and paste it in the module.
5. Press Alt-F11 to return to the spreadsheet.
6. To run the macro, click Tools on the menubar, then Macro,
then
Macros,
and select CustModTable from the list, then Run.
Post back and let me know if the code works for you. If so,
we'll
add
a
button to run it.
James
Sub CustModTable()
Dim FromRow As Long, FromCol As Integer, c As Range, c2
As
Range
Dim ToRow As Long, ToCol As Integer, This As Variant,
This2
As
Variant
Worksheets(2).Activate
Cells.ClearContents
ToRow = 2: ToCol = 1: FromCol = 1
With Worksheets(1)
For FromRow = 1 To .Cells(1, "a").End(xlDown).Row
This = .Cells(FromRow, FromCol)
Set c = Cells.Find(This, LookIn:=xlValues,
lookat:=xlWhole)
If c Is Nothing Then
Cells(ToRow, ToCol) = This
ToRow = ToRow + 1
End If
Next FromRow
[a1] = "Cust"
ToRow = 1: ToCol = 2: FromCol = 2
For FromRow = 1 To .Cells(1, "b").End(xlDown).Row
This = .Cells(FromRow, FromCol)
Set c = Cells.Find(This, LookIn:=xlValues,
lookat:=xlWhole)
If c Is Nothing Then
Cells(ToRow, ToCol) = This
ToCol = ToCol + 1
End If
Next FromRow
For ToRow = 2 To Cells(1, "a").End(xlDown).Row
For ToCol = 2 To Cells(1,
1).End(xlToRight).Column
Cells(ToRow, ToCol) = 0
Next ToCol
Next ToRow
For FromRow = 1 To .Cells(1, "b").End(xlDown).Row
This = .Cells(FromRow, "a")
This2 = .Cells(FromRow, "b")
Set c = Cells.Find(This, LookIn:=xlValues,
lookat:=xlWhole)
Set c2 = Cells.Find(This2, LookIn:=xlValues,
lookat:=xlWhole)
Cells(c.Row, c2.Column) = Cells(c.Row, c2.Column)
+
1
Next FromRow
End With
End Sub
Hi James!
hope you can send me the code cause i need to do it in
macro..using
wizard
doesnt solve my problem.
your help is really appreciated.
--
Regards,
Linda
:
Linda, you have had some good replies. However, if you
still
want
to
do
this with a macro, post back and I'll post the code.
James
news:[email protected]...
Zone said:Hi Linda,
Just happened to run across your post. What is the error message? When
the code chokes, rest the mouse over the word This and see what it is.
James
linda said:Hi James!
its me again,Linda=)
i dont know what happen but i cannot reuse your code anymore.the 1 that
already in use run perfectly but if i want to do it again,the code doesn't
run till end.
the error highlighted on this line:
Set c = Cells.Find(This, LookIn:=xlValues, lookat:=xlWhole)
can u help me if i'am wrong somewhere?
thanks in advanced~
--
Regards,
Linda
Zone said:Glad it worked for you, Linda. Cheers, James
million of thanks,James!its work!
thank you very much;-)
--
Regards,
Linda
:
Linda, yes, I see the problem. Change the last part of the code like
this:
For FromRow = 1 To .Cells(1, "b").End(xlDown).Row
This = .Cells(FromRow, "a")
This2 = .Cells(FromRow, "b")
If This <> "" And This2 <> "" Then
Set c = Cells.Find(This, LookIn:=xlValues,
lookat:=xlWhole)
Set c2 = Cells.Find(This2, LookIn:=xlValues,
lookat:=xlWhole)
Cells(c.Row, c2.Column) = Cells(c.Row, c2.Column) + 1
End If
Next FromRow
End With
End Sub
This is a rather crude patch but it does seem to fix the problem.
James
its true i need to use the if statement:
=IF(Sheet1!A1<>"",Sheet1!A1,"")
even the cell contain null value,but then deep inside its actually
keep
a
function.therefore,when i run CustModTable,the cell contain null
value
return
'1'.
cust mod1 mod2 mod3 1 1 1 1 1 1 1 1 1 1
1
1
1
c1 2
c2 3
c3 2 1
i dont know why its just happen to mod List even both column cust &
mod
actually contain function.
--
Regards,
Linda
:
Hi Linda,
If I understand correctly, the problem is that
=Sheet1!A1
returns a 0 if Sheet1!A1 is empty. To avoid this, use
=IF(Sheet1!A1<>"",Sheet1!A1,"")
James
Hi James!
i just discover a problem using the copy function from your last
post.my 2
columns,cust & mod,contain that copy function because i got the
data
from
other worksheet before it is been analyze.
therefore,when i run macro CusModTable,its include the cell
without
value
since the cell still contain formula(=Sheet1!A1).do you have any
idea
to
solve this?
thanks!
--
Regards,
Linda
:
Glad it works for you, Linda. As a matter of curiosity, how
many
Custs
and
Mods did you have? It should accommodate any reasonable number
of
these.
On your question about copying columns, if you want the copy to
automatically update everytime the original changes, you'll want
to
use
references instead of the actual values. Say your original data
begins
in
cell A1 of Sheet1 and you want your copy to begin in cell A1 of
Sheet3.
In
A1 of Sheet3, type in
=Sheet1!A1
then drag down the column as far as needed. In B1 of Sheet3,
type
in
=Sheet1!B1
and drag down the column as far as needed. I don't see why you
would
need a
macro for this unless the length of the columns is changing when
you
refresh
the data. If so, post back and we'll tackle that. Cheers!
James
Wow,its really amazing!Great job!Your help is really
appreciated,Thanks
James=)
can i ask you some more?how to copy data to another worksheet
using
macro?
i have a worksheet that contains all the raw data[column A-O]
that i
get
from database.i need to copy column B & O to another worksheet
and
from
there
the data will be analyze and display it in a chart.
the raw data will be change everytime i update it and so it
will
update
copied data in the second worksheet and thus update the
chart.can
it
be
done?
--
Regards,
Linda
:
Hi Linda,
This macro assumes that:
1. The original table begins in cell A1 of the first
worksheet
and
has
at
least one blank row after it and at least one blank column to
the
right
of
it.
2. The workbook has a second worksheet available, and the
new
table
will
be
the only thing on the second worksheet.
If these assumptions are correct,
1. Open the workbook in Excel
2. Show the code editor by pressing Alt-F11
3. If the workbook doesn't have a regular code module,
insert
one
by
clicking Insert on the menubar, then Module.
4. Copy the code below and paste it in the module.
5. Press Alt-F11 to return to the spreadsheet.
6. To run the macro, click Tools on the menubar, then Macro,
then
Macros,
and select CustModTable from the list, then Run.
Post back and let me know if the code works for you. If so,
we'll
add
a
button to run it.
James
Sub CustModTable()
Dim FromRow As Long, FromCol As Integer, c As Range, c2
As
Range
Dim ToRow As Long, ToCol As Integer, This As Variant,
This2
As
Variant
Worksheets(2).Activate
Cells.ClearContents
ToRow = 2: ToCol = 1: FromCol = 1
With Worksheets(1)
For FromRow = 1 To .Cells(1, "a").End(xlDown).Row
This = .Cells(FromRow, FromCol)
Set c = Cells.Find(This, LookIn:=xlValues,
lookat:=xlWhole)
If c Is Nothing Then
Cells(ToRow, ToCol) = This
ToRow = ToRow + 1
End If
Next FromRow
[a1] = "Cust"
ToRow = 1: ToCol = 2: FromCol = 2
For FromRow = 1 To .Cells(1, "b").End(xlDown).Row
This = .Cells(FromRow, FromCol)
Set c = Cells.Find(This, LookIn:=xlValues,
lookat:=xlWhole)
If c Is Nothing Then
Cells(ToRow, ToCol) = This
ToCol = ToCol + 1
End If
Next FromRow
For ToRow = 2 To Cells(1, "a").End(xlDown).Row
For ToCol = 2 To Cells(1,
1).End(xlToRight).Column
Cells(ToRow, ToCol) = 0
Next ToCol
Next ToRow
For FromRow = 1 To .Cells(1, "b").End(xlDown).Row
This = .Cells(FromRow, "a")
This2 = .Cells(FromRow, "b")
Set c = Cells.Find(This, LookIn:=xlValues,
lookat:=xlWhole)
Set c2 = Cells.Find(This2, LookIn:=xlValues,
lookat:=xlWhole)
Cells(c.Row, c2.Column) = Cells(c.Row, c2.Column)
+
1
Next FromRow
End With
End Sub
Hi James!
hope you can send me the code cause i need to do it in
macro..using
wizard
doesnt solve my problem.
your help is really appreciated.
--
Regards,
Linda
:
Linda, you have had some good replies. However, if you
still
want
to
do
this with a macro, post back and I'll post the code.
James
news:[email protected]...
linda said:James,
where i need to change my code if i want to place the raw data and the
analyze table in the same worksheet?assuming that i have 1 worksheet.raw
data
in column O and P,while the analyzed data will be place start from cell
A1.i
change your code as below and it run ok,but then the raw data was also
deleted.how to avoid that?cause i want the raw data to be there.
Sub CustModTable()
Dim FromRow As Long, FromCol As Integer, c As Range, c2 As Range
Dim ToRow As Long, ToCol As Integer, This As Variant, This2 As Variant
Worksheets(1).Activate
' Range("A1:G30").Select
Cells.ClearContents
ToRow = 2: ToCol = 1: FromCol = 15
With Worksheets(1)
For FromRow = 1 To .Cells(1, "O").End(xlDown).Row
This = .Cells(FromRow, FromCol)
Set c = Cells.Find(This, LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
Cells(ToRow, ToCol) = This
ToRow = ToRow + 1
End If
Next FromRow
[a1] = "Cust"
ToRow = 1: ToCol = 2: FromCol = 16
For FromRow = 1 To .Cells(1, "P").End(xlDown).Row
This = .Cells(FromRow, FromCol)
Set c = Cells.Find(This, LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
Cells(ToRow, ToCol) = This
ToCol = ToCol + 1
End If
Next FromRow
For ToRow = 2 To Cells(1, "a").End(xlDown).Row
For ToCol = 2 To Cells(1, 1).End(xlToRight).Column
Cells(ToRow, ToCol) = 0
Next ToCol
Next ToRow
For FromRow = 1 To .Cells(1, "P").End(xlDown).Row
This = .Cells(FromRow, "O")
This2 = .Cells(FromRow, "P")
Set c = Cells.Find(This, LookIn:=xlValues, lookat:=xlWhole)
Set c2 = Cells.Find(This2, LookIn:=xlValues, lookat:=xlWhole)
Cells(c.Row, c2.Column) = Cells(c.Row, c2.Column) + 1
Next FromRow
End With
End Sub
thanks=)
--
Regards,
Linda
Zone said:Hi Linda,
Just happened to run across your post. What is the error message?
When
the code chokes, rest the mouse over the word This and see what it is.
James
linda said:Hi James!
its me again,Linda=)
i dont know what happen but i cannot reuse your code anymore.the 1 that
already in use run perfectly but if i want to do it again,the code
doesn't
run till end.
the error highlighted on this line:
Set c = Cells.Find(This, LookIn:=xlValues, lookat:=xlWhole)
can u help me if i'am wrong somewhere?
thanks in advanced~
--
Regards,
Linda
:
Glad it worked for you, Linda. Cheers, James
million of thanks,James!its work!
thank you very much;-)
--
Regards,
Linda
:
Linda, yes, I see the problem. Change the last part of the code
like
this:
For FromRow = 1 To .Cells(1, "b").End(xlDown).Row
This = .Cells(FromRow, "a")
This2 = .Cells(FromRow, "b")
If This <> "" And This2 <> "" Then
Set c = Cells.Find(This, LookIn:=xlValues,
lookat:=xlWhole)
Set c2 = Cells.Find(This2, LookIn:=xlValues,
lookat:=xlWhole)
Cells(c.Row, c2.Column) = Cells(c.Row, c2.Column) +
1
End If
Next FromRow
End With
End Sub
This is a rather crude patch but it does seem to fix the problem.
James
its true i need to use the if statement:
=IF(Sheet1!A1<>"",Sheet1!A1,"")
even the cell contain null value,but then deep inside its
actually
keep
a
function.therefore,when i run CustModTable,the cell contain null
value
return
'1'.
cust mod1 mod2 mod3 1 1 1 1 1 1 1 1 1 1
1
1
1
c1 2
c2 3
c3 2 1
i dont know why its just happen to mod List even both column cust
&
mod
actually contain function.
--
Regards,
Linda
:
Hi Linda,
If I understand correctly, the problem is that
=Sheet1!A1
returns a 0 if Sheet1!A1 is empty. To avoid this, use
=IF(Sheet1!A1<>"",Sheet1!A1,"")
James
Hi James!
i just discover a problem using the copy function from your
last
post.my 2
columns,cust & mod,contain that copy function because i got
the
data
from
other worksheet before it is been analyze.
therefore,when i run macro CusModTable,its include the cell
without
value
since the cell still contain formula(=Sheet1!A1).do you have
any
idea
to
solve this?
thanks!
--
Regards,
Linda
:
Glad it works for you, Linda. As a matter of curiosity, how
many
Custs
and
Mods did you have? It should accommodate any reasonable
number
of
these.
On your question about copying columns, if you want the copy
to
automatically update everytime the original changes, you'll
want
to
use
references instead of the actual values. Say your original
data
begins
in
cell A1 of Sheet1 and you want your copy to begin in cell A1
of
Sheet3.
In
A1 of Sheet3, type in
=Sheet1!A1
then drag down the column as far as needed. In B1 of Sheet3,
type
in
=Sheet1!B1
and drag down the column as far as needed. I don't see why
you
would
need a
macro for this unless the length of the columns is changing
when
you
refresh
the data. If so, post back and we'll tackle that. Cheers!
James
Wow,its really amazing!Great job!Your help is really
appreciated,Thanks
James=)
can i ask you some more?how to copy data to another
worksheet
using
macro?
i have a worksheet that contains all the raw data[column
A-O]
that i
get
from database.i need to copy column B & O to another
worksheet
and
from
there
the data will be analyze and display it in a chart.
the raw data will be change everytime i update it and so it
will
update
copied data in the second worksheet and thus update the
chart.can
it
be
done?
--
Regards,
Linda
:
Hi Linda,
This macro assumes that:
1. The original table begins in cell A1 of the first
worksheet
and
has
at
least one blank row after it and at least one blank column
to
the
right
of
it.
2. The workbook has a second worksheet available, and the
new
table
will
be
the only thing on the second worksheet.
If these assumptions are correct,
1. Open the workbook in Excel
2. Show the code editor by pressing Alt-F11
3. If the workbook doesn't have a regular code module,
insert
one
by
clicking Insert on the menubar, then Module.
4. Copy the code below and paste it in the module.
5. Press Alt-F11 to return to the spreadsheet.
6. To run the macro, click Tools on the menubar, then
Macro,
then
Macros,
and select CustModTable from the list, then Run.
Post back and let me know if the code works for you. If
so,
we'll
add
a
button to run it.
James
Sub CustModTable()
Dim FromRow As Long, FromCol As Integer, c As Range,
c2
As
Range
Dim ToRow As Long, ToCol As Integer, This As Variant,
This2
As
Variant
Worksheets(2).Activate
Cells.ClearContents
ToRow = 2: ToCol = 1: FromCol = 1
With Worksheets(1)
For FromRow = 1 To .Cells(1, "a").End(xlDown).Row
This = .Cells(FromRow, FromCol)
Set c = Cells.Find(This, LookIn:=xlValues,
lookat:=xlWhole)
If c Is Nothing Then
Cells(ToRow, ToCol) = This
ToRow = ToRow + 1
End If
Next FromRow
[a1] = "Cust"
ToRow = 1: ToCol = 2: FromCol = 2
For FromRow = 1 To .Cells(1, "b").End(xlDown).Row
This = .Cells(FromRow, FromCol)
Set c = Cells.Find(This, LookIn:=xlValues,
lookat:=xlWhole)
If c Is Nothing Then
Cells(ToRow, ToCol) = This
ToCol = ToCol + 1
End If
Next FromRow
For ToRow = 2 To Cells(1, "a").End(xlDown).Row
For ToCol = 2 To Cells(1,
1).End(xlToRight).Column
Cells(ToRow, ToCol) = 0
Next ToCol
Next ToRow
For FromRow = 1 To .Cells(1, "b").End(xlDown).Row
This = .Cells(FromRow, "a")
This2 = .Cells(FromRow, "b")
Set c = Cells.Find(This, LookIn:=xlValues,
lookat:=xlWhole)
Set c2 = Cells.Find(This2, LookIn:=xlValues,
lookat:=xlWhole)
Cells(c.Row, c2.Column) = Cells(c.Row,
c2.Column)
+
1
Next FromRow
End With
End Sub
Hi James!
hope you can send me the code cause i need to do it in
macro..using
wizard
doesnt solve my problem.
your help is really appreciated.
--
Regards,
Linda
:
Linda, you have had some good replies. However, if you
still
want
to
do
this with a macro, post back and I'll post the code.
James
message
news:[email protected]...
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.