Posts by libin baby

Hi,
I have dates in a row just wanted a vba code that can copy what evr in wriiten under that date or below the particular date for eg... Row 13 has date 12/12/2016 13/12/2016 14/12/2026
And if i write xxxxx below 12/12/2016 and click on save button it shold copy with the date to sheet2 column wise and overwitre if any entryalready ther

Used the code for save but no use... Plz guide

Hi all,
I was very very successful in making a mini calender in my excel using excel based formulas,and was able to get weeks in another section (eg.. In row B20 i have monday to friday cells), so when user clicks on my mini calender any date that week date falls in b20)
My concern is i wanted a database created were sheet 1 has mini calender when user clickes on any date and below shld be week tat date falls on when user enter the data in that particular date it shld get save in the database and vice versa (it shld it be either way from frontend to backend and backend to front end) and the user is data is hyperlink too..

Re: Iserror err

hi...
thanks for the help ,the same way i wanted the formula to search the blank if the both the formulas output is blank "" then true or else flase
i wanted the formula to be included in the
=IF(and(ISNUMBER(SEARCH("xxx",B1)),VLOOKUP (A1,TABLE AARAY, 12,0)=1),1,"")

Hi team
Was looking for an formula, actually an combination of two formulas
1)if(iserror(search(B1,"XXXX", 1),"",1
2)VLOOKUP (A1,TABLE AARAY, 12,0)
REQUIREMENTS
If value of IF (1st formula) is 1 and vlook vlaue is also 1 then output shd b true or else false

Hi,
Working on something new, no vba this time.

In excel cell A:A ( full A coulmn) i have various unsystematic strings
A1 - SQL_78999_uvhj
A2 - HBU_VVVVV_55555_jjjjj
A3 - THE_ABC123_LOP
A4 - ABC5635_12345_KFG

REQUIREMENT-

1) If there is 5 Contious digit in a sentence get that ( below formula works for me)
2)if something is starting with ABC get that word ( eg ABC 123,ABC5635) (below formula works for me
3) if in a sentence there is both above criteria get both answer in format of / ( eg - answer of A4 should be ABC5635/12345..... ( NEED HELP ON THIS)
4) if nothing is found put " NHA"

Formula used -

=if error (LOOKUP(2,1/(MMULT(0+(ISNUMBER(-MID(" "&A1,ROW(INDIRECT("1:"&LEN(A1)))+{0,1,2,3,4,5,6},1))+0={0,1,1,1,1,1,0}),{1;1;1;1;1;1;1})=7),MID(A1,ROW(INDIRECT("1:"&LEN(A1))),5))
Mid (find ( A1," ABC"), 1)

hi ,
i have column A as
saa-n15-7hy
sma-n1n-8hy
saa-n15-9hy

i wanted all the middle part in cloumn B (excel formaula =MID(A2,5,3)) and last three parts in cloumn C (=MID(A2,9,3)).

below code is not working for me

Sub Right1()




Dim i As Long




Application.ScreenUpdating = False




For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
  Range("B" & i) = Right(Range("A" & i), 5, 3)
Next i




Application.ScreenUpdating = True




End Sub

Re: Code need rectification

Hi sky writer,
The below code

.Range("Q2:Q" & OutputLastRow).Formula = _
"=VLOOKUP(A2,'" & sourceSheet.Name & "'!$A$2:$B$" & SourceLastRow & ",2,0)"
End With

After the vlookup it Gives me the value ( with formula in formula bar) in cloumn Q..
I just need the formula in each cell of Q not to be visible... In short i need my macro to paste special the values in each cell of Q after vlookup.

Hi,
Below code works for me as desired.. It gives me output in Cell Q2:Q.
But i need the out put in PASTE SPECIAL format? any good to approach this?

Sub MakeFormulas()
Dim SourceLastRow As Long
Dim OutputLastRow As Long
Dim sourceSheet As Worksheet
Dim outputSheet As Worksheet


'What are the names of our worksheets?
Set sourceSheet = Worksheets("Sheet1")
Set outputSheet = Worksheets("Sheet2")


'Determine last row of source
With sourceSheet
    SourceLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
With outputSheet
    'Determine last row in col P
   OutputLastRow = .Cells(.Rows.Count, "P").End(xlUp).Row
    'Apply our formula
   .Range("Q2:Q" & OutputLastRow).Formula = _
        "=VLOOKUP(A2,'" & sourceSheet.Name & "'!$A$2:$B$" & SourceLastRow & ",2,0)"
End With
End Sub

i need the below code to be modified little bit

issue 1) the value in A1 and B1 (the user input) is disappearing after macro is run
issue2) if cell 12 already has 1 in it , it should add 1 if the below criteria is met

Sub Timing()
Dim ws As Worksheet




Set ws = Sheet3
Sheet3.Range("A1").Value = Tim1
Sheet3.Range("B1").Value = tim2


' dbTime = Time


If Tim1 > 14 Or tim2 < 24 Then




ws.Cells(1, 12).Value = 1
ws.Cells(1, 13).Value = 1
ws.Cells(1, 14).Value = 1




ElseIf Tim1 > 17 Or tim2 < 24 Then
ws.Cells(1, 15).Value = 69
Else
ws.Cells(1, 16).Value = 69
End If
End Sub

[COLOR="#0000CD"]Moderation Edit: [/COLOR]Please add code tags to your VBA syntax .. by highlighting the code and Clicking the # button