Posts by ashu1990

ashu1990 · Feb 13th 2015

Re: Make HTML table lines visible via vba

have you tried my code and then use your code for extracting the data from mail it should do the trick try using debug mode go step by step and see if the strParameter comes with the data and so on...

ashu1990 · Feb 13th 2015

Re: Make HTML table lines visible via vba

i used your code and it worked fine for me it took the table criteria and the staus as you described...

what is the issue are you getting any error?

ashu1990 · Feb 13th 2015

Re: Using a cell value as File name reference

Agreed with luke..

was trying the same and came to the same conculsion as luke's

ashu1990 · Feb 13th 2015

Re: Make HTML table lines visible via vba

try this

HTML

Private Sub Command11_Click()
       
    Set oApp = CreateObject("Outlook.application")
    Set oMail = oApp.CreateItem(olMailItem)
    oMail.HTMLBody = "<html> <head> <style> table, th, td {border: 1px solid black;} </style> </head> <body> <p>Dear reader,</p><p>Please find below information on the letter.</p> <p>Any queries please let us know</p><p>Regards</p> <TABLE><TR><TD>Rating:</TD><TD>1</TD></TR><TR><TD>BIN:</TD><TD>1234567</TD></TR><TR><TD>Company name:</TD><TD>Something Ltd.</TD></TR><TR><TD>Status:<TD>New</TD></TABLE> </body> </html>"
    oMail.Subject = "Status on case with BIN nr: "
    oMail.To = "Doodle, Jhon"
    oMail.BodyFormat = 2
    oMail.Display
    Set oMail = Nothing
    Set oApp = Nothing
End Sub

Display More

ashu1990 · Feb 13th 2015

Re: Block cell to change after a certain time.

hope this helps...

Code

ActiveSheet.Unprotect "Pass"
If Format(Now(), "HH:MM:SS  AM/PM") < "2:00:00 PM" Then Range("C3").Locked = True
ActiveSheet.Protect "Pass"

ashu1990 · Feb 12th 2015

Re: Array with dates

just use this and change the range as per your's :smile:

Code

For Each cell In Range("A2:A51")
If Day(cell) < 15 Then
cell.Offset(0, 1).Value = Format(DateAdd("m", -1, cell), "MMM")
Else
cell.Offset(0, 1).Value = Format(cell, "MMM")
End If
Next cell

ashu1990 · Feb 12th 2015

Re: remove cross link formula from another workbook

on which line do you get the error

ashu1990 · Feb 10th 2015

Re: remove cross link formula from another workbook

have a look at this

http://www.ozgrid.com/forum/sh…92542&p=735208#post735208

ashu1990 · Feb 10th 2015

Re: ActiveX controls doesn't work anymore from 01/01/2015 on XP

this is mainly because of the microsoft updates..

have a look at this to resolve the issue http://stackoverflow.com/quest…activex-controls-disabled

ashu1990 · Feb 10th 2015

Re: Array with dates

Code

a = [{"01/18/2015", Jan; "01/19/2015", Jan]

you have used "a" as array where as you are using "v" for output value?

Code

for each cell in WS.Range(WS.Range("A1"), WS.Cells(LR, 3)) 
        cell.EntireRow.Cells(Found.Column + 2).Value = IIf(IsError(v), "", v) 
 next cell

ashu1990 · Feb 4th 2015

Re: Loop which continues until the value in the cell is 0

Didn't know your exact requirement but i think this should give you a way to acquire what you want

Code

Sub test()


Workbooks(wb2).Activate
Range("A2").Select
Do While ActiveCell.Value <> ""
If ActiveCell.Value > 0 Then ActiveCell.Copy (Workbooks(wb1).Range("B3")): Application.Run RunAll()
ActiveCell.Offset(1, 0).Select
Loop
 
End Sub

Display More

ashu1990 · Feb 4th 2015

Re: copy cells with number to the end of a column on another worksheet

try this

Code

Dim lr2 As Long
Dim rCell2 As Range
 
 
lr2 = Cells(Rows.Count, 1).End(xlUp).Row
 
 
For Each rCell2 In Range("C2:C" & lr2)
     
     
    If IsNumeric(rCell2) = True Then
         
         
        rCell2.Copy Sheets("Sheet2").Range("A" & Rows.Count).End(3)(2)
        
    ElseIf (rCell2 Like "*#*") And (rCell2 Like "*[a-zA-Z]*") Then
         
         rCell2.Copy Sheets("Sheet2").Range("A" & Rows.Count).End(3)(2)
         
    End If
     
     
Next rCell2

Display More

ashu1990 · Feb 3rd 2015

Re: Splitting Excel 2010 files into multiple Excel 2003 files

Quote

weNew.SaveAs Filename:=StrFileName, FileFormat:=56

should it be wbnew?

ashu1990 · Feb 3rd 2015

Re: Splitting Excel 2010 files into multiple Excel 2003 files

my bad didn't saw you were using .close use saveas instead

Code

wbNew.saveas Filename:=ThisWorkbook.path & "\App_Invetory" & "-" & lCopy & ".xls" ,fileformat:=56

ashu1990 · Feb 3rd 2015

Re: Splitting Excel 2010 files into multiple Excel 2003 files

Try this

Code

wbNew.Close SaveChanges:=True, Filename:=ThisWorkbook.path & "\App_Invetory" & "-" & lCopy & ".xls" ,fileformat:=56

fileformat makes the workbook saved into the format you described.

ashu1990 · Jan 30th 2015

Re: code to create multiple graphs through selection

attach a workbook with examples this will help indeed...

ashu1990 · Jan 30th 2015

Re: Switching rows and columns in access

copy the first column and paste in some where on the new sheet and use pastespecial values ticked and transpose ticked and enter you will get what you want...

ashu1990 · Jan 29th 2015

Re: Vba Function for a counter

can't you match the details with a unique concanate of invoice number and amount and date with the main summary workbook from where you have to fetch the data and paste it into your workbook.

ashu1990 · Jan 29th 2015

Re: Vba Function for a counter

hello rafdepet welcome to the forum...

please use code tag's when ever posting code it will help the person to see the code more easier way its a # sign on the right hand corner of the quick reply..

for your question you cannot store the number in function from where you can retrieve it as after your macro finishes the code it deletes all the variables stored into it, you can check the last invoice number where you store the data eg:- if you have the invoice number into a sheet in column "A" then use

Code

lst_number = range("A65536").end(xlup).value

so no need of storing it in another cell or you can use a find method to see if the invoice number entered is already present eg:-

Code

dim ran as range


set ran = range("A:A").find(what:= yournumber ,matchcase:=false) ''<--- checking the value in range("A:A")


if not ran is nothing then  ''<--- if found then do your code work




end if

Display More

hope this helps...

ashu1990 · Jan 23rd 2015

Re: Open workbooks and save as csv

it should be

Code

ActiveWorkbook.SaveAs Filename:= _ 
(strP & "\YOURFILENAME.csv") _ 
, FileFormat:=xlCSV, CreateBackup:=False

ur using (strP & "\*.csv") where the filename should be present asuming ur strp has the full path name and "\FILENAME.csv" the * should be replaced by the filename