Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

getting the data in between the cells dynamically

Posted on 2005-04-15
47
Medium Priority
?
204 Views
Last Modified: 2008-02-01
im retreiving data dynamically from oracle db and the data is displayed something like

1     test
1     test
1     test

so now i want to display someother data in between these data and also group them, something like

1     test
stn nr       desc
1     test
stn nr       desc
stn nr       desc
1     test
stn nr       desc

---------------------------

rec.Open "SELECT * FROM project_data3 where ang_kost1='" & clk & "'", conn

While Not rec.EOF

   i = i + 1
   Tabelle2.Cells(i, 1) = (rec.Fields("ver_nr1").Value)
   Tabelle2.Cells(i, 2) = (rec.Fields("ver_bes1").Value)

   rec.MoveNext
Wend
0
Comment
Question by:vihar123
  • 26
  • 21
47 Comments
 
LVL 12

Expert Comment

by:gbzhhu
ID: 13790148
Very simple change


While Not rec.EOF
   i = i + 1
   Tabelle2.Cells(i, 1) = (rec.Fields("ver_nr1").Value)
   Tabelle2.Cells(i, 2) = (rec.Fields("ver_bes1").Value)
    i = i + 1                  'this makes room for the values you want to enter
   Tabelle2.Cells(i, 1) = (rec.Fields("New_Field_Here").Value)
   Tabelle2.Cells(i, 2) = (rec.Fields("New_Field_Here").Value)

   rec.MoveNext
Wend
0
 

Author Comment

by:vihar123
ID: 13790213
how can i group them dynamically and also give them color, i mean to the rows
0
 
LVL 12

Expert Comment

by:gbzhhu
ID: 13790278
What do you mean group them?  show an example of how you them to display

For the colour select the column you want to color like this

Tabelle2.Cells(i, 1).select

Then color it like this

    With Selection.Interior
        .ColorIndex = 6
        .Pattern = xlSolid
    End With
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:vihar123
ID: 13790353
by grouping i mean doing this control automatically

top menu - Data - group and outline - group
0
 

Author Comment

by:vihar123
ID: 13790411
i = i + 1
   Tabelle2.Cells(i, 1) = (rec.Fields("ver_nr1").Value)
   Tabelle2.Cells(i, 2) = (rec.Fields("ver_bes1").Value)
i = i + 1                  'this makes room for the values you want to enter
   Tabelle2.Cells(i, 1) = (rec.Fields("New_Field_Here").Value)
   Tabelle2.Cells(i, 2) = (rec.Fields("New_Field_Here").Value)

leaving the above two i want to colour the entire row
0
 
LVL 12

Expert Comment

by:gbzhhu
ID: 13790434
OK.  Here

    Range("A23:A33").Select
    Selection.Rows.Group

If you don't know where the range ends, the value of variable i after the while loop ends will contain the last row we put data in, so you could do

    Range("A23:A" & i).Select
    Selection.Rows.Group
0
 
LVL 12

Expert Comment

by:gbzhhu
ID: 13790463
To color entire row

Tabelle2.Cells(i, 1).entirerow.select

    With Selection.Interior
        .ColorIndex = 6             'This is yellow, what color do you want
        .Pattern = xlSolid
    End With
0
 

Author Comment

by:vihar123
ID: 13790512
>>>'This is yellow, what color do you want
       
i want light blue
0
 

Author Comment

by:vihar123
ID: 13790543
when im selecting a new one its throwing error at this line >>>Selection.Rows.Group
as the group methd of the range object cannot be called


0
 
LVL 12

Expert Comment

by:gbzhhu
ID: 13790545
All the blur colors I can see are
8,5,28,42,34,33,32

Try these numbers one at a time to see which one suits you best.  Just change ColorIndex

ColorIndex = yournumberhere
0
 

Author Comment

by:vihar123
ID: 13790568
and also the grouping is remaining, how can i make a new grouping with each selection, and when nothing is selected then no grouping shld be visible
0
 
LVL 12

Expert Comment

by:gbzhhu
ID: 13790603
I don't understand this.  what do you mean "with each selection".  Do you mean the user selects a row, a column or several rows maybe.  Explain a bit more.  It is not really easy to imagine what you have in mind without verbose explanation
0
 

Author Comment

by:vihar123
ID: 13790665
oops sorry

well im having two comboboxes, one us having order number and other is having suborder numbers, combining both im taking as a value and retreiving data from a view, so each sub order has different data, so this data i want to display in the format of

ver_nr1      desc

stn_nr      details        year
stn_nr      details        year

ver_nr2      desc

stn_nr      details        year
stn_nr      details        year

and to these data i want to colour and group data depending on ver_nr
so ver_nr1 will become one group and 2 will become another group

0
 

Author Comment

by:vihar123
ID: 13790794
well how can i delete or take out grouping before a new group is made
0
 
LVL 12

Accepted Solution

by:
gbzhhu earned 2000 total points
ID: 13790856
To remove grouping

    Range("A23:A33").Select
    Selection.Rows.Group

0
 

Author Comment

by:vihar123
ID: 13808826
sorry was out for some time

thanks a lot
0
 

Author Comment

by:vihar123
ID: 13813687
just one more thing, how can i take out colouring??

with first one some cells are coloured and with the second one, the old one is remaining

i want the cells to be coloured only till the values are there
0
 
LVL 12

Expert Comment

by:gbzhhu
ID: 13813727

    With Selection.Interior
        .ColorIndex = xlColorIndexNone              
        .Pattern = xlSolid
    End With
0
 
LVL 12

Expert Comment

by:gbzhhu
ID: 13813733
You need to use the help as all this info is in th help file.  Make sure you have VBA help installed with Excel (it doesn't install by default) then just highligh a word a press F1.  For example if you highlight ColorIndex and press F1 it will tell you the color values
0
 

Author Comment

by:vihar123
ID: 13813789
>>>> With Selection.Interior
        .ColorIndex = xlColorIndexNone              
        .Pattern = xlSolid
    End With

i mean with every selection, old values and old colour shld go away and new values and colour should come
0
 

Author Comment

by:vihar123
ID: 13813793
i tried using help and also some macros, but they arent so helpful
0
 
LVL 12

Expert Comment

by:gbzhhu
ID: 13813826
Can you post what the code look like now and put comment on where you want the colors be removed
0
 

Author Comment

by:vihar123
ID: 13813837
my code is something like this, but im not able to clear the contents and colour with every new selection, i dont know where im going wrong


Private Sub CommandButton3_Click()

Dim clk As String
Dim i As Integer

clk = ComboBox1.List(ComboBox1.ListIndex) + "." + ComboBox2.List(ComboBox2.ListIndex)

MsgBox clk

Dim conn As ADODB.Connection
Dim rec As ADODB.Recordset


Set conn = New ADODB.Connection

conn.Open "Driver={Microsoft ODBC for Oracle};" & _
                   "Server=;" & _
                   "Uid=;" & _
                   "Pwd=;"

   Set rec = New ADODB.Recordset

rec.Open "SELECT * FROM project_data3 where ang_kost1='" & clk & "'", conn
i = 22

ActiveCell.Offset(1, 0).Range("A1").Select
Range("A24:A" & i).Select
Selection.ClearContents
Selection.ClearOutline

 Tabelle2.Cells(i, 1) = (rec.Fields("ver_nr1").Value)
  Tabelle2.Cells(i, 2) = (rec.Fields("ver_bes1").Value)

While Not rec.EOF

   i = i + 1
   Tabelle2.Cells(i, 1) = (rec.Fields("stn_nr1").Value)
   Tabelle2.Cells(i, 3) = (rec.Fields("ua_nr1").Value)
   Tabelle2.Cells(i, 4) = (rec.Fields("psp1").Value)
   Tabelle2.Cells(i, 6) = (rec.Fields("prod_bes1").Value)
   Tabelle2.Cells(i, 7) = (rec.Fields("ve1").Value)
   
   

Range("A24:A" & i).Select
Selection.ClearOutline
Selection.Rows.Group


 Tabelle2.Cells(i, 1).EntireRow.Select

    With Selection.Interior
        .ColorIndex = 8
        .Pattern = xlSolid
         
    End With
   
 
   rec.MoveNext
Wend






rec.Close
Set rec = Nothing

End Sub
0
 

Author Comment

by:vihar123
ID: 13813845
with every new click i want to display new contents and colour, i mean only the rows with contents shld be coloured and
0
 
LVL 12

Expert Comment

by:gbzhhu
ID: 13813924
If there are no other data on the sheet use this

Private Sub ComboBox1_Click()
    Tabelle2.UsedRange.Select
    Selection.Clear
    Selection.ClearFormats
End Sub

If there other data then you must clear only the range you want

Private Sub ComboBox1_Click()
    Tabelle2.range("Put your range here").Select
    Selection.Clear
    Selection.ClearFormats
End Sub

0
 

Author Comment

by:vihar123
ID: 13813930
is my question clear????
0
 

Author Comment

by:vihar123
ID: 13813969
if you see my code, i just want to replace the range, for eg:-

when im selecting one value from combobox and pressing button, im displaying valies on the cells dynamically, and these cells are having the values and are coloured, now when i select a new one, im able to clear the values and put the new ones but im not able to clear the colour,

what i want is the rows are coloured only till the values are there, so everytime i need to clear the colour and colour it new

0
 
LVL 12

Expert Comment

by:gbzhhu
ID: 13814004
I thinnk it is now.  I will post in 5 to 10 mins I am in the middle of something now
0
 

Author Comment

by:vihar123
ID: 13814035
ok
0
 
LVL 12

Expert Comment

by:gbzhhu
ID: 13814106
Try this

After the line

MsgBox clk

add this

    Rows("22:" & i).Select
    Selection.Interior.ColorIndex = xlNone
    Selection.ClearContents
0
 

Author Comment

by:vihar123
ID: 13814144
Rows("22:" & i).Select
    Selection.Interior.ColorIndex = xlNone
    Selection.ClearContents


im getting error as type incompatible
0
 
LVL 12

Expert Comment

by:gbzhhu
ID: 13814178
Sorry.  You already have code that clears content like this

Range("A24:A" & i).Select
Selection.ClearContents
Selection.ClearOutline

add this line after that code
Selection.Interior.ColorIndex = xlNone

If you still get the error replace the line I gave above with
Selection.Interior.ColorIndex = xlColorIndexNone

If it is not covering the range you want then adjust the 24 to something else 22 or 23

0
 
LVL 12

Expert Comment

by:gbzhhu
ID: 13814186
I am off to lunch, back in an hour or so
0
 

Author Comment

by:vihar123
ID: 13814206
i have tried both, its not clearing the colour

................

Tabelle2.Range("a23:a500").Interior.ColorIndex = 2
Tabelle2.Range("b23:b500").Interior.ColorIndex = 2
Tabelle2.Range("c23:c500").Interior.ColorIndex = 2

i dont know right or wrong, im doing something like this and its working,
0
 
LVL 12

Expert Comment

by:gbzhhu
ID: 13814757
Put a breakpoint on the line after
MsgBox clk

Then tell me the value of i at that point - do msgbox i

Also where I said this
Selection.Interior.ColorIndex = xlNone

instead try
Selection.ClearFormats

0
 

Author Comment

by:vihar123
ID: 13815039
Private Sub CommandButton3_Click()

Dim clk As String
Dim i As Integer

clk = ComboBox1.List(ComboBox1.ListIndex) + "." + ComboBox2.List(ComboBox2.ListIndex)

MsgBox clk
MsgBox i

value of i is 0
0
 
LVL 12

Expert Comment

by:gbzhhu
ID: 13815135
Stupid me!!!!!

Change
Dim i As Integer

to
Static i As Integer
0
 

Author Comment

by:vihar123
ID: 13815220
the value of i is changing according to the last cell, but the colour is not going

i mean i selected one value and it coloured 20 cells and i showed value 20 and
i selected another value it had around 15 values, and the remaining 15 - 20 cells are still having colour
0
 
LVL 12

Expert Comment

by:gbzhhu
ID: 13815253
Please post code again as it is now from beginning up to this line

While Not rec.EOF
0
 

Author Comment

by:vihar123
ID: 13815341
heres my complete code

Private Sub CommandButton3_Click()

Dim clk As String
Static i As Integer

clk = ComboBox1.List(ComboBox1.ListIndex) + "." + ComboBox2.List(ComboBox2.ListIndex)

MsgBox clk
'MsgBox i

Dim conn As ADODB.Connection
Dim rec As ADODB.Recordset

Set conn = New ADODB.Connection

conn.Open "Driver={Microsoft ODBC for Oracle};" & _
                   "Server=;" & _
                   "Uid=;" & _
                   "Pwd=;"

   Set rec = New ADODB.Recordset

rec.Open "SELECT * FROM project_data3 where ang_kost1='" & clk & "'ORDER BY stn_nr1 ASC", conn
i = 22

ActiveCell.Offset(1, 0).Range("A1").Select
Call clear_rows
Range("A24:A" & i).Select
Selection.ClearContents
Selection.ClearOutline
Selection.ClearFormats


While Not rec.EOF
Tabelle2.Range("b22") = (rec.Fields("ver_nr1").Value)
Tabelle2.Range("e22") = (rec.Fields("ver_bes1").Value)
   i = i + 1
   Tabelle2.Cells(i, 1) = (rec.Fields("stn_nr1").Value)
   Tabelle2.Cells(i, 3) = (rec.Fields("ua_nr1").Value)
   Tabelle2.Cells(i, 4) = (rec.Fields("psp1").Value)
   Tabelle2.Cells(i, 6) = (rec.Fields("prod_bes1").Value)
   Tabelle2.Cells(i, 7) = (rec.Fields("ve1").Value)
   Tabelle2.Cells(i, 8) = (rec.Fields("ve1").Value)
 
Range("A24:A" & i).Select
Selection.ClearOutline
Selection.Rows.Group
Selection.HorizontalAlignment = xlCenter
Selection.Font.Bold = True

 Tabelle2.Cells(i, 1).EntireRow.Select

    With Selection.Interior
        .ColorIndex = 8
        .Pattern = xlSolid
         
    End With
   
    rec.MoveNext
Wend



rec.Close
Set rec = Nothing
0
 
LVL 12

Expert Comment

by:gbzhhu
ID: 13815398
This block of code

Range("A24:A" & i).Select
Selection.ClearContents
Selection.ClearOutline
Selection.ClearFormats

Should go after
msgbox clk

Do that and let me know what you get
0
 

Author Comment

by:vihar123
ID: 13815478
its working only for the first A column
0
 
LVL 12

Expert Comment

by:gbzhhu
ID: 13815649
OK

Change this
Range("A24:A" & i).Select
to
Range("A24:D" & i).Select
0
 

Author Comment

by:vihar123
ID: 13815954
ok its working.......thanks a lot
0
 
LVL 12

Expert Comment

by:gbzhhu
ID: 13815973
Cool.  Please if you need another help with this open a new question.  I helped as much as I can on this one
0
 

Author Comment

by:vihar123
ID: 13825140
ya ill open a new question,

thanks a lot again
0
 

Author Comment

by:vihar123
ID: 13833261
Hello  gbzhhu

could you please refre to this new post, thanks

http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_21397231.html
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month11 days, 20 hours left to enroll

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question