Posts by Rachit

Re: Chart In A Macro

Actually i am working on a analysis project and the file would not be present initially.. its only after 5-6 steps of analysis that the required data is generated (It involves many worksheet to be created and deleted). So it is not possible to record a macro for myself.. So can you help me now?

I have lots of data located in 2 coulumns of a worksheet named "Result" at C6:C1000 and D6:D1000. I want to create a scatter plot in new worksheet named "Chart" using a macro. How can i do it?? Please also provide comments in the macro.

Re: Importing From Some Other File

I have recently started to use excel and hence am unable to understand it. Can you please write the macro for me here?

Hello,
I have made a macro to import the a excel file into a new file after truncating few initial rows
Here is the code for it:

Sub Import()
' Collect folder location, file name and sheet name from user


Dim Prompt1, Prompt2, Prompt3, myDir, myFile, mySheet As String
 Dim ClCnt As Long  'column counter
Prompt1 = "Enter the location of input file:"
myDir = InputBox(Prompt1)
Prompt2 = "Enter filename:"
myFile = InputBox(Prompt2)
Prompt3 = "Enter sheetname:"
mySheet = InputBox(Prompt3)


' Source file open and copy contents after deleting 32 header lines
' Retain column headers from the input file


With Workbooks.Open(myDir & myFile).Sheets(mySheet)
     ClCnt = .Range("[B][U]A33:CB33[/U][/B]").SpecialCells(xlConstants).Count ' Count the number of columns present
     With .Range("[B][U]a33[/U][/B]", .Range("a" & Rows.Count).End(xlUp)).Resize(, ClCnt)
          ThisWorkbook.Sheets(1).Range("a1").Resize(.Rows.Count, .Columns.Count).value = .value
     End With
     ActiveWorkbook.Close False
End With


End Sub

But i want to generalize this macro for every input file. In that case my starting row will not be the same as it is in this case (33 here). I want to ask the user for the starting row and then use it in this code to copy the data. What change can be made in this macro??

Re: Log Function In A Macro

Thanks a lot!

Re: Log Function In A Macro

Thanks, It is working fine. Just one more thing... I want on the log base 2

Re: Log Function In A Macro

Thanks for the reply.
But this will not help me.

I am actually making a bigger macro which includes taking LOG of the values. As i am still a begginer with VB can you help me with exact code for taking input from A2:A200 and output at B2:B200

Hello,
I have a lots of number arranged in a column. I want to take log of each number (on the base 2) and show the result in the adjacent column. I want this to be in a macro and the results to be displayed all at a time (I dont want to drag the cursor down to get log values for number corresponding to each row).

Re: Total Cell Count In A Sheet

Thanks

Re: Total Cell Count In A Sheet

Thanks. But you have not given the way to count column.

HI.. can anyone tell me how can i count the number of filled rows and columns (containing both characters and numbers) in a worksheet through a macro??. Please do not tell the individual functions like count or CountA.

Re: Filtering By Name

It is not able to do what i want. I want to copy filtered sheet in new worksheet without any tags hanging from top.

Re: Filtering By Name

Hey, Please answer my question.

I have a excel of about 5000 rows and 80 columns. Each row corresponds to a particular set of experiment. I want to reduce the number of rows by removing useless experimnets like controls etc. I want to search the row for some key words(ex. control) and if it present then remove it. It should prompt user to enter the keyword and the original file should not be disturbed. It can be copied to 2nd worksheet. Is such a code possible??

Re: Closing The Spreadsheet By Macro

Thanks It worked

I have been working on a macro

Sub test1()
Dim myDir As String, fn As String, sn As String
myDir = "C:\Documents and Settings\user\Desktop\rachit miRNA analysis project\Data\"
fn = "2620_TotC_A3_SynC_A5_0003.xls"
sn = "2620_TotC_A3_SynC_A5_0003"
With Workbooks.Open(myDir & fn).Sheets(sn)
     With .Range("a33", .Range("a" & Rows.Count).End(xlUp)).Resize(, 50)
          ThisWorkbook.Sheets(1).Range("a1").Resize(.Rows.Count, .Columns.Count).Value = .Value
     End With
     .Close False
End With
End Sub

But it is showing error with ".Close False" line. When i remove this line the original file from which i am copying is not getting closed. What can i add in the macro to do it