Posts by orion23

orion23 · Apr 21st 2016

Re: Excel VB Convert Bulk Cell Formula To VB Code

Hi,
My thought is it would be much faster to process the data when using vba and also to prevent someone accidentally deleted cell formulas as the worksheet contained lot of formula.

Thank you.

orion23 · Apr 21st 2016

Re: Search & Copy Data without Open Workbook

Hi, anyone can enlighten me pls

Thank you

orion23 · Apr 21st 2016

Hi,

Need some help here, i have a spreadsheet contained many formulas in different cells, i know i can use macro recorder to convert them to vb code, but there are too many of them, eg. more than 50 formulas, so i need to manually select the cell formula, copy and paste in each cells, repeat the process and all will capture in recorder, but doing it for more than 50 formula is not a good idea.

So is there a way to copy and paste the formula and convert all of them in bulk to vb code?

i have been searching over the internet but have not seen anything about bulk conversion to vb code.

would appreciate your help and advice.

thank you.

orion23 · Apr 19th 2016

Hi,

Need some help here, i want to search and copy data, below vb code did what i want on same workbook but i have one question here, let say the data stored in another workbook, how can i search and copy the data without open the workbook? what is the additional vb code and how to integarte with vb code below.

would appreciate your help.

Thanks

Code

Sub searchdatacopy()
Dim erow As Long
Dim ws As Worksheet
Dim lastrow As Long
Dim count As Integer




lastrow = Sheets(“item_price”).Cells(Rows.count, 1).End(xlUp).Row
count = 0
For x = 2 To lastrow




If Sheets(“item_price”).Cells(x, 1) = Sheet2.Range(“B3”) Then
Sheet2.Range(“A11”) = Sheets(“item_price”).Cells(x, 1)
Sheet2.Range(“B11”) = Sheets(“item_price”).Cells(x, 2)
Sheet2.Range(“C11”) = Sheets(“item_price”).Cells(x, 3)
count = count + 1
End If




Next x
If count = 0 Then
Set ws = Worksheets(“sheet3”)
erow = ws.Cells(Rows.count, 1).End(xlUp).Offset(1, 0).Row
ws.Cells(erow, 1) = Date
ws.Cells(erow, 2) = Sheet2.Range(“B3”)
Sheet2.Range(“A11:C11”).ClearContents
End If




End Sub

Display More

orion23 · Mar 22nd 2016

Re: Excel regular text box font change color

Hi,

Ya, I put text boxes to point some info in graph, some graph color overlay the textbox, so was thinking to switch the textbox font color base on cell values

Thanks for your reply

orion23 · Mar 21st 2016

Hi,
i have many regular text boxes with its values link to cells.

I want to change the font color in the text boxes based on the cell value, eg. Below 80 is red, above 81 is green.

I have searched thru the wen but couldn't find much info about it, anyone can enlighten me, thank you.

Note: I use the regular text boxes, not the one in developer form control.

orion23 · Mar 18th 2016

Re: Excel VB Prompt MsgBox If No Data Found

Hi Luke,

Thank you so much, it work!

orion23 · Mar 17th 2016

Hi all excel guru,

Need some help as i am still learning excel vb, i have below vb code and try to copy data from sheet2 to sheet1 if the input value (C1) match the cell ranges, i would like to put a msgbox if no matching input value in C1, so how to do this and would appreciate your help.

Thank you.

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

Code

Sub searchdata()
Dim erow As Long
Dim ws As Worksheet
Dim lastrow As Long
Dim count As Integer




lastrow = Sheets("sheet2").Cells(Rows.count, 1).End(xlUp).Row




For x = 2 To lastrow
If Sheets("sheet2").Cells(x, 2) = Sheet1.Range("c1") Then
Sheet1.Range("d5") = Sheets("sheet2").Cells(x, 2)
Sheet1.Range("d6") = Sheets("sheet2").Cells(x, 3)
Sheet1.Range("f5") = Sheets("sheet2").Cells(x, 4)
Sheet1.Range("d7") = Sheets("sheet2").Cells(x, 13)
Sheet1.Range("d11") = Sheets("sheet2").Cells(x, 13)
Sheet1.Range("d12") = Sheets("sheet2").Cells(x, 13)








End If




Next x


end sub

Display More

orion23 · Mar 13th 2016

Re: Excel LARGE Function & Index Match Function Error

hi thanks for your reply and it work!

I am going thru the code and tying to understand them a bit.

Thanks

orion23 · Mar 13th 2016

Hi,

I am trying to use LARGE function to rank the sales values and do an indexmatch for the sales person, when the sales values are identical, the indexmatch returned same sales persons name, anyone know how to fix this?

attached is the excel file and jpeg.

Thanks
[ATTACH=CONFIG]68561[/ATTACH]

orion23 · Mar 8th 2016

Re: Excel VB Copy & Print Range in Bulk

Hi Raha,

thanks bro.

one more question if i have a long list (100 records in sheet 'summary' and want to copy each of them in this template and print as jpg, is it something like loop thru each of the record and copy, print as jpeg, so can pls enlighten me how to implement this?

Thanks

orion23 · Mar 8th 2016

Re: Excel VB Copy & Print Range in Bulk

Hi Raha,
Thanks but I didn't ask to change the font size bro, can you pls check thread#9 above.

Thanks.

orion23 · Mar 7th 2016

Re: Excel VB Copy & Print Range in Bulk

Hi Raha,

Need your advice below

pls have a look on the excel file - tab "pic_format", this is the template i want to print as jpeg from A1:F8.

i need to copy 2 variables - sales person name and sales data from a summary list, assuming now i already copied all data, next is to export this template to jpeg based on various records data, i could have many records like 50, 100.....

so i want to sort out the export jpeg vb code here, would appreciate you help.

thanks.

orion23 · Mar 7th 2016

Re: Excel VB Copy & Print Range in Bulk

Hi Raha,
Can you share which part of vb code to print the range as jpg?

if i want to enlarge the area of print area (eg. A4:E8), only show one record/account (eg. account #123, 456, 789,....) , below the record i will add some text on my own.

so the vb will run a list from top to bottom of all the account #

i had read thru your code and a kinda confused.

Thanks again.

orion23 · Mar 6th 2016

Re: Excel VB Copy & Print Range in Bulk

Hi Raha,
Thank you so much, it's working great!! awesome!!

i am looking thru the vb code and try to understand them, thanks and appreciate your help.

orion23 · Mar 6th 2016

Re: Excel VB Copy & Print Range in Bulk

Hi,

I attached the xlsm file, module 1 is the test(), module 2 is the print jpeg.

would appreciate your help.

orion23 · Mar 6th 2016

Re: Excel VB Copy & Print Range in Bulk

Hi Raha,
Thanks for your reply, sorry maybe my previous explanation not clear, i want to print the summary tab (range A4:E5) as jpeg for each individual account#, if i got 40 account#, i want to have 40 jpeg file, example attached

[ATTACH=CONFIG]68490[/ATTACH][ATTACH=CONFIG]68491[/ATTACH].

so let say now i can use summary tab data as the main data source, next is create a print format (A4:E5) to print each individual account# as jpeg.

each of jpeg file name based on account# in cell A5 and save in drive D.

Any idea how to do it?

Thank you so much.

orion23 · Mar 6th 2016

Hi excel guru,
Need some help, i need to run a project to copy data (sales & outstanding) on daily basis and later print the summary range as jpeg file as output.

I have figured out how to copy and print them, now i am stuck in the process of how to print them in bulk at one go, ie. let say i have 40 accounts to process daily, i want the VB to match the account# in tab sales & outstanding, and output each of the 40 accounts as individual jpeg file.

i have attached the file here for better understanding

where am i now and cracking my head, would appreciate you help.

Thank you.