Posts by Yoman

    Re: Output Used Range To Text File

    Thanks for the quick reply Dave

    But I have 2 question:

    1) will this code change or delete the contents in the active sheet?

    1. myrange.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlLeft

    As changing anything in the active sheet is a no no.

    2) How do I want to make sure the contents that is in the column begin with "NOT" is not copy across to the text file?

    I have started some code earier, is this any good?

    I would like to output the text I perpared in "sheet 1" into a text file, "output.txt"

    The imformation I like to output start from coulmn C, and column D then column E.....
    The numbers of rows in each column could be different, and also there may be some single empty column in between. The totals numbers of coulmn is unknow.

    the text in the output.txt should contant all the imformation in the sheet started from column C, coulmn D, column F.... (if coulmn E is empty). and the text in the output.txt file is continues with no gap in it.

    To make it even more powerful for later use, The column that begin with NOT should not be output.

    Excel Data-


    Many thanks

    Re: Vlookup To Ouput -1 Column

    the question is corrected as column.

    And I found the answer from another seach of the question bank.


    many thanks for fast reply

    I know how to do Vlookup and get the value on the +1 column as following

    =VLOOKUP(50, D5:E19, 1)

    What about I would like it return the value of -1 Column.

    Can I using some function of Offset with other functions?

    I required to do some Monte Carlo analysis for 1000000 simulation.
    I have managed to find some free code, however, the time it took to run 1000000 >30min. Is that normal?

    The code that it took the longest to run is following:

    1. For i = 1 To number_of_trials
    2. Application.Calculate
    3. For j = 1 To number_of_formulas
    4. runs(j, i) = sel.Cells(1, 1 + j)
    5. Next j
    6. Next i

    Is there any way I can implove this code to make it run faster?

    I have already tried Application.ScreenUpdating = False

    Re: Wavelength Of An Xy Plot

    Thank for the code. It work great. This site is best.
    But I made a mistake on the worksheet I attached. The First column and second column should be the X-value, not the number of rows within the array. trying to debug it now.

    many thanks

    Re: Wavelength Of An Xy Plot

    The X-Y data on the left is the raw data. The first column is X value where the Y value begin to get more then zero. and second column is the x value where Y-data return to zero. Hence the distance between them is wavelength. The raw data is the X-Y data.

    as you can see with the example rae data, there is only 3 set of data. However, the real data may have 1000's of those set within the raw data. and the hightlighted area can be 1000's rows.

    Thankyou for the help

    Re: Wavelength Of An Xy Plot

    I was looking for something to put all data in hightlighted area automaticly. as the real data can be 1000's row long. it will take long long time to input the data manually.

    Is there an easy way to find the wavelength and maximum value within the wave length easily for a x-y plot? Can anyone give me some direction on how to do this? I know a liitle bit of VBA buy just a beginner.

    I have attached a example of the excel sheet, the highlighted section is the results I want. The real data could be 10000 rows with 100's set of wavelength.

    Please feel free to ask extra question to clarify the question if needed.


    • wavelength.xls

      (14.85 kB, downloaded 113 times, last: )

    I would like a macro to copy the data in Raw1 and Raw2 from sheet1 into sheet2. but with a space row between data as shown in attrachement.

    the number of rows in Raw1 and Raw2 is always the same. but the number of rows can be very big and unknown.

    I have named the title of Raw1 as "raw". becasue I want the macro will still work when I insert a column before it.

    what is the easiest way to do this task?

    Many thanks


    • Book1.xls

      (13.82 kB, downloaded 81 times, last: )

    Re: Scroll The Scale Of The Graph

    Quote from Andy Pope

    Can you post an example chart. Does not have to contain real data but the data should be realistic.

    I have followed the example from your site. However, when I scroll the bar. when the start period is 50. the scaleing in x-axis is far from perfet. The idea case would be that the scaling of the x-axis only cover the data range (so no gap at both side of graph).

    futhermore. how can I set the maximum value in the scroll bar as (maximum xData - Number of Period) in VBA, so that the template can be use for any data without manual setting?


    I have a big set of data, where I requires to zoom in to view the details of the graph (by scaling the X and Y axis)

    I am thinking of a graph that can use 4 scroll bar to control the zooming.

    2 for each axis, where one control the difference between the maximum and minmum value (Zooming effect, minimum value always the same), and one to scroll the range of the data without zooming, (maximum - minimum) always the same.

    I belived someone must have done this before, can someone kindly attrach me an example please.


    It will be even better if only 2 scroll is required to control the x axis as above, and the Y-axis will be automatic set to the maximum and minimum of the data that is shown in the grapth.

    Below is part of my code that I belive it will save the TxtArray(i) into a .csv file. However, Can I change the .csv into .inp?
    .inp and opening a .inp file with notepad is same as opening up a .csv file.

    1. Open myFile For Output As #1
    2. For i = 1 To UBound(TxtArray)
    3. Print #1, TxtArray(i)
    4. Next i
    5. Close #1
    6. Msg = "File saved as:" & vbCrLf
    7. Msg = Msg & myFile
    8. MsgBox Msg, vbOKOnly, "CSV File"

    Many Thanks

    I want to define the range from a given cell and all data below within the same column
    strDataStart is the named cell in the worksheet that want to start from

    Below is what I tried and failed.

    Many thanks