This all needs to be done, from a separate file where I have a command button, I press the button and then the data is pulled from file 1 which has yellow, blue and orange data, and then pushed into a new file say test.xls
both files are closed and have separate sheets as well.
Please let me know or help me with the code.
I am good with C++ bt never really worked with VB, worked with VC++ nd other languages.
so, all, i need is, if someone can help me with the code.
Many thanks,
Unknown
Last edited by unknowninator; Feb 15th, 2012 at 11:11 AM.
Reason: Sticky Notes
Re: [EXCEL] using VBA copy one field from a column and concatenate with transpose
there would be several ways to do this
regardless of the code what would you consider to be the simplest method to achieve the result?
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: [EXCEL] using VBA copy one field from a column and concatenate with transpose
The simplest way to do it wud be,
to copy the id's first into a new sheet.
and take the row data into the same sheet.
viz.
1yelow
2blue
3orange
4
5
6
7
now, multiply them one by one into a new sheet viz.
1yellow
2yellow
3yellow
4yellow
.
.
.
and the pull value from the main file corresponding to 1 yellow.
we need not worry about order, since the order will remain the same if we copy them as is.
Re: [EXCEL] using VBA copy one field from a column and concatenate with transpose
next questions then...
where in new sheet, what is sheet name, do colours go on different sheets, are there a known number of colours and rows or is every thing quite arbitrary?
Re: [EXCEL] using VBA copy one field from a column and concatenate with transpose
Saved in a new workbook actually, which is just an interim one, since its going to be used for various processes and then closed eventually outputting the results into a third file.
No. of rows can vary and so can the no. of columns.
the sheet name can be anything, doesnt matter.
actually, its just a part of a very big calculation being performed every month, taking into account data from previous and current month.
this is jst like 5% of the whole project.
so, all i need to do is, put them into a new sheet and then progress from there.
Re: [EXCEL] using VBA copy one field from a column and concatenate with transpose
there are always other ways to do these things!
anyway this solution requires a function that can be told a few factors rather than one that works out what to do, the first version will be a sight easier to construct.
Re: [EXCEL] using VBA copy one field from a column and concatenate with transpose
makes sense...
I was wondering, is it beneficial to call functions in a subroutine or make various subroutines ??
bt yeah.... if u can give sme pseudo code... i can tweak nd play around wid it...
am asking this... coz am using a form for the big program, and already have a few subroutines.... was wondering, which is the best way to optimize code and efficiency....
Re: [EXCEL] using VBA copy one field from a column and concatenate with transpose
here is a bit of code that works.. to get at the data
Code:
Sub translate_group_data(id As String, first_row As Integer, last_row As Integer, first_data As String, last_data As String, step_size As Integer)
'fix column values
fd = Range(first_data & "1").Column
ld = Range(last_data & "1").Column
For Group = fd To ld Step step_size
For r = first_row To last_row
Debug.Print Range(id & r).Value & Cells(2, Group - 1).Value & vbTab & Cells(r, Group).Value
Next r
Next Group
End Sub
you need to calll it like this... based on your example.xls
Code:
translate_group_data "a",4,6,"c","i",3
as you see it can access the appropriate information, now we just need to output the data...